# Count All the Things

February 16, 2021

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:

- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- 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:

`=COUNT(A2:A10)`

And you’ll see that the result is `3`

.

More on the COUNT function from Microsoft

## COUNTA - All Non-Blank Cells

If you need to count *anything* that is not blank, then use `COUNTA`

.

`=COUNTA(A2:A10)`

This will return `8`

, which is everything *except* the blank cell.

More on the COUNTA function from Microsoft

## COUNTBLANK - C’mon, you know what this is

Ok, so what about counting blanks? Use this formula:

`=COUNTBLANK(A2:A10)`

Which returns `1`

.

More on the COUNTBLANK function from Microsoft

## COUNTIF and COUNTIFS - Counting Based on Criteria

What if you have a specific thing or set of things you want to count?
That’s where `COUNTIF`

and `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 `COUNTIFS`

.

If you wanted to count only items that are less than 2, use this:

`=COUNTIF(A2:A10,"<2")`

Which returns `1`

.

Count items that are `TRUE`

:

`=COUNTIF(A2:A10,TRUE)`

Also returns `1`

.

Count items that have `hello`

:

`=COUNTIF(A2:A10,"hello")`

Again, returns `1`

.

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, ...)`

All `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
than `4`

, you would do this:

`=COUNTIFS(A2:A10,">1",A2:A10,"<4")`

Which returns `2`

.

More on the COUNTIF function from Microsoft

More on the COUNTIFS function from Microsoft

## 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:

`=COUNT(IF(ISERROR(A2:A10),1,""))`

The way this works is that the `ISERROR`

function returns a bunch of `TRUE`

or `FALSE`

values,
depending on if the cell that is being evaluated has an error result. Then,
for each `TRUE`

, we put a `1`

in the result, and for each `FALSE`

, we put a
blank string `""`

.

Here’s what the partial result looks like before `COUNT`

is applied:

`=COUNT({"","","","","","",1,1,1})`

Since `COUNT`

doesn’t count blank strings, then only the `1`

s are counted,
and we have a result of `3`

, which is how many errors are in the data.