Excel formula to Find the last cell with value in a row and add to adjacent cell if there aren'-CodePudding

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.

CodePudding user response：

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!!!

CodePudding user response：

I tried the above however it just returned 1 both on O1 and P1. Am I missing something?