It’s inevitable that you will need to count things in Excel from time to time. How many sales were there? How many tickets were issued? How many customer complaints did we have last month?
Easy, you just use the
COUNT function. You start typing
=COUNT into a cell
and then Excel presents you with a whole host of count functions:
Which count function should you use?
For this article, we’ll focus on these functions:
- Bonus: Counting Errors
And for reference, here’s the data set we’ll be working with. You can copy and paste this in Excel in cell A1.
Data 1 2 3 TRUE hello =1/0 =NA() =SUM("hi")
COUNT - Numbers Only
This only counts numbers, which is important to remember because sometimes
you will want to count other things like text or
TRUE/FALSE or you may
even want to count error values.
Put this formula in a cell:
And you’ll see that the result is
COUNTA - All Non-Blank Cells
If you need to count anything that is not blank, then use
This will return
8, which is everything except the blank cell.
COUNTBLANK - C’mon, you know what this is
Ok, so what about counting blanks? Use this formula:
COUNTIF and COUNTIFS - Counting Based on Criteria
What if you have a specific thing or set of things you want to count?
COUNTIFS come in. You can use these to
count numbers and non-numbers.
If you have just one criteria to use, then use
COUNTIF. If you have
more than one criteria, then use
If you wanted to count only items that are less than 2, use this:
Count items that are
Count items that have
Now, if you have multiple criteria, use
COUNTIFS. The trick here is to remember
that the formula accepts the params list like this:
=COUNTIFS(range_1, criteria_1, range_2, criteria_2, ...)
range_N items must be the same size. And also important to remember
is that ALL criteria must be met in order for the cells to be counted.
So I you wanted to find out how many cells are greater than
1 but less
4, you would do this:
Bonus: Counting Errors
One extra item I wanted to cover here is how to count errors (I mean, pretty much everything else was covered).
Unfortunately, there’s no
COUNTERROR function, but that’s ok. We’ll build one:
The way this works is that the
ISERROR function returns a bunch of
depending on if the cell that is being evaluated has an error result. Then,
TRUE, we put a
1 in the result, and for each
FALSE, we put a
Here’s what the partial result looks like before
COUNT is applied:
COUNT doesn’t count blank strings, then only the
1s are counted,
and we have a result of
3, which is how many errors are in the data.