Happy Friday Excellers. It’s #formulafriday time. Today let’s look at counting all of the negative values in a data range. 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 Representatives have had to give in a period of one months Sales of Widgets. First let’s look at the syntax of the SUMIF function.
SUMIF Function Syntax.
=SUMIF(range, criteria, [sum_range])
range – is a required argument and is the range of cells you want evaluated by the criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format.
criteria– is a required argument and is the cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format
sum_range – is optional, and is the actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
Let’s Work Our SUMIF Example.
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 example we can see that there are 22 negative values in the data set, equating to 22 refund sales by our sales representatives.
Let’s Turn Up The Volume On This Formula
…….let’s find out how much those refunds amount in in monetary value. We can simply amend the formula to
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.
Do you want to learn more formulas, in a fast and fun way?
75 of the most frequently used Excel formulas explained with examples. Get your GO FASTER STRIPES with this fantastic E- Book.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Formula Friday Series you can do so by clicking on the link below.