If you need to sum all the negative numbers in Excel, a good function to know is the SUMIFS function. It has multiple uses and generally easy to learn, but telling it to look at negative (or positive numbers for that matter) involves some small tricks.
Summing all negative numbers
As shown below, we want to sum the total of the negative numbers in the profit line and then the positive numbers in the profit line.
We can make use of the SUMIFS function with one trick for indicating to SUMIFS to look out for negative or positive numbers.
Summing and Criteria are the same
First trick is that the sum range and the criteria range are the same and you need to enter both. So Excel now knows that eventually it will sum the profit line, but first it needs to look for something in that same profit line.
The second trick is how you enter the criteria. As shown below, you enter <0 but very important you need to surround it with inverted commas “”. So it must look like this
in the Criteria area of the function.
Other uses for this SUMIFS trick
There are a number of other uses for this SUMIFS trick. All you must remember is that the Criteria must end up looking like a logical test and MUST be surrounded by inverted commas.
So we can do as above and sum all the negative numbers in Excel. We could also create a benchmark, e.g. sum all the numbers that are above 10, for example, by changing the above criteria to
“>10” (don’t forget the inverted commas).
Point the SUMIFS criteria to look at other cell contents
What if you want to sum all the numbers that are above/ below a certain number, but the number you want to use is variable and will be in a cell e.g. cell C11 below.
You can’t enter
because Excel will try find cells with text bigger than C11 e.g. the text C12 and not for what is inside cell C11.
This is where you need to create what Excel wants i.e. the end result shown in the red circle below must be “>10” in this case, but it needs to change when the cell changes. This is where a CONCATENATE comes into play.
As shown above, by using CONCATENATE (or CONCAT or &), we can create what Excel wants but still make it variable and looking at the contents of a cell.
This way you can use SUMIFS to look for bigger than or less than any number sign or any number and make it variable.