This formula is based on the SUMIF Function. I have to say it is one of THE most useful functions I find in Excel. Used to calculate conditional sums, and in the example we will look at today, we will count all of the negative values in a data range.
I have set up an example to look at the number of refunds that Sales Reps have given in a period of one months Sales of Widget-1’s.
The formula syntax is =SUMIF(E4:E10,”<0″) and in our example below it will sum all of the negative numbers in cell range E4:E10
In the exmaple we can see that there are 22 negative values in the data set.
Let’s turn up the volume on this yes?, let’s find out how much those refunds amount in in monetary value. We can simply amend the formula to
=SUMIF(E4:E10,”<0″,D4:D10). This will give us the count of values in the range but return the return the sum of the corresponding values in column D.
So, we know there were 22 refunds with a value of -550.
Other Excel Tips
4. Need Excel Templates?…Look no Further