 SOLVED

Highlighted

# 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 sum to the same row irrespective of whether the number is positive or negative.  The answer in Column B should be \$31 and the answer in Column C should be (\$63).  I would appreciate any thoughts.

5 Replies
Highlighted

# Re: Index Match row problem

You may use this formula in B11, copied across to C11:
=SUMPRODUCT(B1:B10*(\$A1:\$A10>0))
Highlighted

# Re: Index Match row problem

@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.

Highlighted
Solution

# Re: Index Match row problem

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))
Highlighted

# Re: Index Match row problem

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.
Highlighted

# Re: Index Match row problem

To understand what each formula argument returns, select it then press F9. Thereafter, press Ctrl+Z to revert to the original formula. I’m delighted to have helped you accomplish your goal.