Happy Friday Excellers. It’s #formulafriday time therefore time for another blog post in my Excel series. Today let’s look at counting all of the negative values in a data range. This formula is based on the SUMIF Function. So, I have to say it is one of THE most useful functions I find in Excel. It is 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.
For example I have set up a sample data set 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.
The SUMIF Function Syntax.
=SUMIF(range, criteria, [sum_range])
where
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).
Working The SUMIF Example.
The formula syntax is
=SUMIF(E4:E10,”<0″) inn 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
So, 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.
Finally, in conclusion we know there were 22 refunds with a value of -550. Easy as that. Would you use this in your Excel work. Share in the cemments below.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
A Full list Of Formula Friday Blog Posts
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
