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:
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)
The formula we will construct is:
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.
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!