Forum Discussion
Solved
Index Match row problem
In the attached spreadsheet, I have set up the formula to sum the set of numbers in column A until a negative number is encountered in row 5. I need to have the formula in the adjacent columns to su...
- I haven’t tested, but you may try, this formula in B11, copied across to C11:
=SUM(B1:INDEX(B1:B10,
MATCH(-1,INDEX(SIGN($A1:$A10),
0),0)-1))
You may use this formula in B11, copied across to C11:
=SUMPRODUCT(B1:B10*($A1:$A10>0))
=SUMPRODUCT(B1:B10*($A1:$A10>0))
TwifooThank you for responding. Your answer worked given the information I gave earlier but I realize now that the parameters were not set up correctly. I've attached another spreadsheet with more accurate parameters. If I use your formula with the new set of numbers, the returned answer is not correct.
- I haven’t tested, but you may try, this formula in B11, copied across to C11:
=SUM(B1:INDEX(B1:B10,
MATCH(-1,INDEX(SIGN($A1:$A10),
0),0)-1))- That worked. Thank you very much. I'm going to have to dig into the formula to gain full understanding but it definitely accomplished what I wanted.
