• 410K Members
• 8,489 Online
• 466K Conversations
SOLVED

New Contributor

# 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

# Re: Index Match row problem

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

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

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

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

# 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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies