How To Sum The Absolute Value Based On A Criteria In Excel

Here we are going to sum the absolute value of a list of numbers based on a criteria from another list in Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Our challenge today is to sum up the ABSOLUTE VALUE of the numbers in column “F”, only if the numbers in column “E” are greater than 0:

abs1

As a quick refresher, the absolute value of a number is the number without the positive or negative sign. So, the absolute value of 6 is 6, while the absolute value of -5 is 5.

To accomplish our goal we are going to use the following functions:

– SUMPRODUCT (we can also use SUM in this instance and achieve the same result)
– IF
– ABS

The formula we will construct is:

{=SUMPRODUCT(IF((E4:E19)>0,ABS(F4:F19)))}

Notice the curly brackets. That’s because this is an ARRAY formula. As you can see in the formula above, we are not just checking to see if one number or cell is greater than 0, but the entire array. The same goes for the application of the ABS function – it is being applied against the entire array of numbers, not just a single cell. Therefore, you must enter this formula with Ctrl + Shift + Enter, not just Enter.

Let’s use the Evaluate Formula option in Excel to walk through how this formula works.

First it will evaluate the array for the IF function so that it can determine which of the values is greater than 0:
abs2

After listing the array, it will compare each to see which is greater than 0…:
abs3

…and indicate with a TRUE or FALSE for each comparison. Next, Excel will apply the ABS formula to the array in column F:
abs4

Now, Excel will apply the IF statement. Where there is a TRUE, we will see the number, while a FALSE will just return FALSE – which is equivalent to zero:
abs5

Here Excel will sum the results…:
abs6

…and we get our answer:
abs7

And there you have it:
abs8

SUMPRODUCT can be useful in so many ways such as “summing” up the “product” of the arrays, adding up the results after multiplying one array times the other. Do a search in the box in the right column and you will come up with at least 4 other posts detailing this very useful function.

Hope this is useful for you, and Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

8 Responses to How To Sum The Absolute Value Based On A Criteria In Excel

  1. Mike Benstead March 26, 2014 at 5:49 pm #

    Love your work!

    • Michael Rempel March 26, 2014 at 7:42 pm #

      Thanks, Mike! Really appreciate it! Hope you spread the word!

  2. Steve D November 24, 2014 at 6:16 pm #

    Hi Michael,

    How would I sum values that are positive or negative but ONLY if the absolute value of the entry is larger than another number?

    Say I have the numbers 1,5,-4,-5,9,7,-11

    I only wish to add those numbers where the absolute value is > 5, say. So I would add 9+7-11 = 5

    Many thanks in advance!

    • Michael Rempel November 24, 2014 at 9:54 pm #

      The easy way is to use a helper column and use the ABS function to enter the absolute value of those cells, then use the formula =SUMIF(F3:F9,”>5″,E3:E9).

      The way to do it with one formula is the following: {=SUMPRODUCT(((ABS(E3:E9))>5)*(E3:E9))}
      Note the curly brackets so it is an array formula, so you have to enter it with Ctrl + Shift + Enter

      • Steve D November 25, 2014 at 1:37 pm #

        Thanks for the quick reply, Michael! I’m unfamiliar with using Sumproduct as you’ve shown, so I’ll have to dig into that.

        Having said that, I’m not able to easily add a helper column (they solve a lot of frustrations, don’t they?) as this is a “canned” file that will be used by others and have data extracted. And if some other user nukes the helper column, things go bad very quickly.

        I did some experimenting and was able to find a nested expression (arrayed) that fits the bill. Assuming the test value (it was “5”, originally) is to be a variable – and it is – I put it in cell C2, and the array formula that works is:

        {=SUM(IF(ABS(B3:B33)>C2,B3:B33,0))}

        Works like a charm. Array formulas: the interesting and useful Excel feature that nobody seems to know about… 🙂

        • Michael Rempel November 25, 2014 at 1:53 pm #

          Steve, That solution works also! Nice job. That’s the great part about Excel. There are so many ways to accomplish the same result, and none are really wrong, just different! This might end up being another post!

  3. cma June 19, 2015 at 11:24 am #

    How would I make this work if column E contained text values, and I wanted to sum absolute values in column F where column E contained – but did not equal – specific text criteria. For example, I want to sum absolute values in F, where E contains “EX” (e.g., EX1, EX2, EX3).

    I can use an asterisk in my formula to get a net total, as follows:
    =SUMIF($E:$E,”EX*”,$F:$F)

    When I use the asterisk in your formula, I get zero:
    {=SUMPRODUCT(IF(($E:$E)=”EX*”),ABS($F:$F)))}

    This formula also results in zero:
    {=SUMPRODUCT(IF(($E:$E)=LEFT(“EX”,2),ABS($F:$F)))}

    Any ideas? Thanks!

    • Michael Rempel June 19, 2015 at 5:20 pm #

      Try this: {=SUM(IF(LEFT((E4:E19),2)=”EX”,ABS(F4:F19)))}

Leave a Reply