Sum Positives and Absolute Value Negatives with Multiple Criteria

[dharmaskibum]

I am trying to compose a formula that will sum positives and absolute value negatives, but only if the row they are in meets one of several criteria.

For just totaling, I use =SUMPRODUCT(SUMIF(A1:A10,F1:F3,B1:B10)) where F1:F3 lists the preset criteria. Works great.

I have tried using =SUMPRODUCT(ABS(SUMIF(A1:A5,F1:F3,B1:B5))); however, this calculates the sum of each array, then adds them as absolute values.

How do a calculate the absolute value of the array first? Am I going about this wrong?

[JBeaucaire]

Wouldn't the ABS() go around the B1:B5 ?

[JBeaucaire]

My bad, here, try this:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A5, F1:F3, 0)), ABS(B1:B5))

[Haseeb Avarakkan]

Or, use SUMIFS,

=SUMPRODUCT(SUMIFS(B1:B10,B1:B10,{">0","<0"},A1:A10,F1:F3)*{1,-1})

[dharmaskibum]

Thank you gentlemen. Both options work perfectly.

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.