SOLVED
Home

Index Match row problem

papabear89
New Contributor

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
You may use this formula in B11, copied across to C11:
=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.

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