I wanted to continue building on my popular series on how to use IF statements and nested IF statements with “and, or, not” in Excel by discussing two additional and very useful functions called countif and sumif. Surprisingly, they’re not as widely used as you’d expect, and sometimes knowing which to use is a bit confusing.
Countif is a function that will count (as in 1, 2, 3…) the number of entries within a range if certain criteria are met, and the data within each entry can be text or numbers.
Sumif, on the other hand, is a function that will add the total number of numerical entries within a range if certain criteria are met. This means that sumif only works with numbers when adding up figures, so if your range includes any text, those will be ignored. Let’s walk through a simple example.
Suppose you have the following simple table of sales by type and # for each year from 2003-2009:
And what you’d like to know is how many sales of each type (in your store vs. by phone vs. online) were done in total. Countif will help you figure that out quickly. First, let’s enter the type of sale we’re interested in in a column to the right:
Next, we’ll type in the “countif” function. As you can see, countif takes two inputs, the range you’re interested in and the criteria that need to be met:
In our case, the data (store type) is in column B, so we input that into the first part of the countif function:
Next, we want each entry to be counted only if the type of sale was “store”, or what we’ve typed into cell E2:
Note that if we hadn’t typed in “store” in E2, we could type it directly into the function like this:
By the way, remember to always put quotation marks (“”) around the criteria when working with text!
Either way, once we close the parenthesis and hit
Repeating the same thing for “telephone”:
We find the same result — 7 sales. Now let’s check “online”:
Turns out we had less online sales (maybe because we didn’t launch a website that accepted sales until 2005):
That’s fine and dandy, but not particularly useful info. How about if we wanted to know the total number of sales by type for the entire period? In that case, we’d want to use the sumif function.
For “store” sales, we’d enter in sumif and the appropriate inputs as
follows. First, sumif takes in slightly different inputs than countif.
The first input is the range that you’re interested in, very similar to countif:
The second input is the criteria, which in this case is also identical to what we put in for countif above:
But sumif also takes one additional argument: the range to be summed. In this case, we want to know the total number of sales, which is in column C:
Closing out the parenthesis, we find that we had 211 total sales in the store from 2003-2009:
Again, let’s repeat the same thing to find the total number of telephone sales:
…and online sales:
See the difference between what countif gives and what sumif gives?
Note that sumif can also take in different criteria. Let’s suppose we wanted to know the total number of sales that have taken place starting in 2008, regardless of type. To do this, we could make our range the year, found in column A:
and then add in the criteria of “>= 2008”:
Finally, we again want to sum up the total number of sales, which is in column C:
Closing out our final parenthesis, we get that we have had a total of 141 sales from 2008-2009:
Finally, just a quick example here will show what happens if you put in non-numerical data to sum using the sumif function. Let’s change the number of store sales in 2008 from “37” to “nonsense” and see what happens:
Notice how the calculations automatically ignore that entry, and the total number of store sales and the total number of sales since 2008 both drop by 37.
That’s it for now. A quick intro tutorial to using countif and sumif. Like most other Excel functions, these can get as complicated as you’d like (at the expense of readability), so always try to keep it as simple as possible.