SOLVED

Index Match row problem

%3CLINGO-SUB%20id%3D%22lingo-sub-394670%22%20slang%3D%22en-US%22%3EIndex%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394670%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20spreadsheet%2C%20I%20have%20set%20up%20the%20formula%20to%20sum%20the%20set%20of%20numbers%20in%20column%20A%20until%20a%20negative%20number%20is%20encountered%20in%20row%205.%26nbsp%3B%20I%20need%20to%20have%20the%20formula%20in%20the%20adjacent%20columns%20to%20sum%20to%20the%20same%20row%20irrespective%20of%20whether%20the%20number%20is%20positive%20or%20negative.%26nbsp%3B%20The%20answer%20in%20Column%20B%20should%20be%20%2431%20and%20the%20answer%20in%20Column%20C%20should%20be%20(%2463).%26nbsp%3B%20I%20would%20appreciate%20any%20thoughts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394776%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394776%22%20slang%3D%22en-US%22%3ETo%20understand%20what%20each%20formula%20argument%20returns%2C%20select%20it%20then%20press%20F9.%20Thereafter%2C%20press%20Ctrl%2BZ%20to%20revert%20to%20the%20original%20formula.%20I%E2%80%99m%20delighted%20to%20have%20helped%20you%20accomplish%20your%20goal.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394763%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394763%22%20slang%3D%22en-US%22%3EThat%20worked.%20Thank%20you%20very%20much.%20I'm%20going%20to%20have%20to%20dig%20into%20the%20formula%20to%20gain%20full%20understanding%20but%20it%20definitely%20accomplished%20what%20I%20wanted.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394750%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394750%22%20slang%3D%22en-US%22%3EI%20haven%E2%80%99t%20tested%2C%20but%20you%20may%20try%2C%20this%20formula%20in%20B11%2C%20copied%20across%20to%20C11%3A%3CBR%20%2F%3E%3DSUM(B1%3AINDEX(B1%3AB10%2C%3CBR%20%2F%3EMATCH(-1%2CINDEX(SIGN(%24A1%3A%24A10)%2C%3CBR%20%2F%3E0)%2C0)-1))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394720%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EThank%20you%20for%20responding.%26nbsp%3B%20Your%20answer%20worked%20given%20the%20information%20I%20gave%20earlier%20but%20I%20realize%20now%20that%20the%20parameters%20were%20not%20set%20up%20correctly.%26nbsp%3B%20I've%20attached%20another%20spreadsheet%20with%20more%20accurate%20parameters.%26nbsp%3B%20If%20I%20use%20your%20formula%20with%20the%20new%20set%20of%20numbers%2C%20the%20returned%20answer%20is%20not%20correct.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394690%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20row%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394690%22%20slang%3D%22en-US%22%3EYou%20may%20use%20this%20formula%20in%20B11%2C%20copied%20across%20to%20C11%3A%3CBR%20%2F%3E%3DSUMPRODUCT(B1%3AB10*(%24A1%3A%24A10%26gt%3B0))%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted
You may use this formula in B11, copied across to C11:
=SUMPRODUCT(B1:B10*($A1:$A10>0))
Highlighted

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