I am trying to find a formula in excel which will find the last cell that has a value and then it to check if the adjacent cells have value and if it has values then it should show the sum until it reaches a blank cell in the reverse direction on cell O1 and also should show the count of the cell in P1.
So, this what I have tried and works, use the below formulas, to accomplish the output you are looking for,
• Formula used in cell O2 --> To get the sum of the values from back until a blank has been found towards left.
=SUM(INDEX(B2:M2,MATCH(2,1/ISBLANK(INDEX(B2:M2,1):
INDEX(B2:M2,MATCH(2,B2:M2,1)))))
:INDEX(B2:M2,MATCH(2,B2:M2,1)))
• Formula used in cell P2 --> To get the counts.
=COUNT(INDEX(B2:M2,MATCH(2,1/ISBLANK(INDEX(B2:M2,1):
INDEX(B2:M2,MATCH(2,B2:M2,1))))):
INDEX(B2:M2,MATCH(2,B2:M2,1)))
Note: Array formulas needs to be committed with CTRL+SHIFT+ENTER and fill down!!!