The Function COUNT Explained:
Count Cells In Excel (Easily)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Of all the things that Excel does, counting seems rather rudimentary.

But below the surface, there’s so much more to it!

I guarantee you’re going to be surprised by the power of Excel’s COUNT function 🙂

And when you get into COUNT’s sister functions, there are some really cool things you can do.

Let’s take a look at COUNT, and then get into how you might use this function—and related functions—to get more valuable data from your spreadsheet.

The basics of COUNT

The COUNT function is rather simple. You give it a range of cells, and it tells you how many of those cells contain numbers.

By default, the function counts numbers, dates, and textual representations of numbers (like “1” and “250”).

It’s a rather simple idea. And the syntax is just as straightforward:

The syntax of COUNT

=COUNT(value1, [value2]…)

The first argument, value1, is the item or range in which you’d like to get a count. This will almost always be a range of cells.

Additional arguments can be provided to tell Excel to count the cells in multiple ranges. These are optional, but you can include as many as you want.

Get your FREE exercise file

If you want to follow along—which we strongly recommend—you can download the free exercise file below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

A COUNT example

Let’s take a look at an example to see how COUNT could be useful. Our example worksheet contains a short set of survey results.

Question #2 asked respondents to rate their satisfaction with a product.

Most people responded with numbers, but a few responded with text. Let’s use COUNT to figure out how many people submitted numerical responses.

First, click into cell F2 and type “Number of numerical ratings Q2:” so we don’t forget what we used this function for.

Next, click into G3 type “=COUNT(” to start the formula:

count-setup

Then click and drag to select the values of column C. Don’t include the header cell, or it will be included in the count.

In this case, including the header cell won’t make a difference.

But it could in future COUNTs, so we’ll start getting in the habit now.

Kasper Langmann, Co-founder of Spreadsheeto
count-click-drag

Finally, close off the parentheses and hit Enter.

It doesn’t get much simpler than that!

count-function-result

Let’s get some more information from our spreadsheet. Question #4 also asked respondents to rate their satisfaction.

How would you go about counting the number of numerical responses in both of these combined?

Again, click into a new cell; we’ll use G5. Use the same process as above; type “=COUNT(“, then select the values in column B.

Instead of closing off the formula here, though, type a comma and select the values in column D.

count-multiple-columns

Now close off the parentheses and hit Enter.

count-multiple-columns-result

You now have the number of numerical cells in both columns.

Counting non-empty cells

If you want to count cells that contain any type of data, instead of just numbers, you’ll need COUNT’s sister function, COUNTA.

This is even simpler than COUNT. It returns the number of non-empty cells in a range or multiple ranges.

If you use COUNTA on column B in the example spreadsheet, you’ll see that it counts everything, and gives you a total count of 50.

Column D, however, contains two blank cells and will result in a COUNTA of 48.

count-vs-counta

Using COUNT the fast way

Because counting numerical and non-empty cells is a common task, Excel has made it easy for you.

Highlight one of the columns in the spreadsheet, and look at the status bar near the bottom-right corner of the Excel window:

status-bar-count-excel

You’ll see the count displayed in the status bar, and if you’ve highlighted numerical data, you’ll also see the average and the sum.

The count displayed here is the equivalent of COUNTA; it counts all non-empty cells.

But what if you want to only count numerical values? You can do that too.

Just right-click on the status bar, and select Numerical Count.

status-bar-count-options

Now you’ll see the numerical count (the equivalent of the COUNT function) displayed as well.

Adding power with COUNTIF

What if you don’t want to count everything, though?

If you want to count a specific subset of cells, COUNTIF will help you out. (If you’re familiar with AVERAGE and AVERAGEIF, COUNTIF does the same type of thing.)

This function lets you specify a specific criterion, and it will count the number of cells that meet it.

For example, let’s look at column B, Gender. You might want to get a count of the number of Ms and Fs in this column. COUNTIF can help.

Here’s the syntax:

The syntax of COUNTIF

=COUNTIF(range, criterion)
The range is where Excel will look for cells that meet the criterion.
You can use anything that resolves to TRUE or FALSE for the criterion. Greater than, less than, equals, text matching, and all of the other types of operators you’re used to will work here.

As always, click into a cell (we’ll choose F9) and type “Number of males:” to make sure you remember what the result represents.

Kasper Langmann, Co-founder of Spreadsheeto

Then, click to cell H9 and start the function with “=COUNTIF(“.

countif-setup2

Select column B for the range:

countif-range

And, finally, type a comma and enter the criterion. In our case, that will simply be “M”.

countif-criterion

Close off the formula and hit Enter.

countif-result

We now have a count of the cells that contain “M”. You can repeat with “F” to find the number of female respondents.

This could be used in many other ways as well.

If you wanted to find out how many ratings were above five, for example, or how many people wrote “good” as their rating, COUNTIF can tell you.

Kasper Langmann, Co-founder of Spreadsheeto

Counting unique values with COUNTIF

If you have duplicate values in your data, and you want to find out how many unique values there are, COUNTIF can help with that too. It just needs a little help from SUMPRODUCT.

Let’s start with the syntax for counting unique values. In our example workbook, we’ll count the number of unique values in column A to find out how many different people took the survey.

Here’s the formula we’ll use:

=SUMPRODUCT(1/COUNTIF(range, range))

We’ll start on the inside. COUNTIF, when given a range both as the range and criterion, returns a series of values in an array. By using each value as the divisor in a fraction, and using SUMPRODUCT to combine those fractions, we get the number of unique values in a range.

Let’s run this formula on the Name column.

unique-value-setup

As you’ll see, the results of COUNTA and our unique-value-counting formula are different. That difference is equal to the number of duplicates in the column.

unique-values-results

It’s important to remember that blank cells will cause this particular formula to throw a #DIV/0! error.

To prevent this, use COUNTIF(range, range&” “). This will include blank cells in the count. If you need more flexibility with blank cells, try using the FREQUENCY function.

If you want to find out what you can do with duplicates, check out our guide to finding, merging, and deleting duplicates in Excel!

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping things up…

COUNT seems like a rather simple request of Excel at first, but once you see the power of the counting functions, you’ll find all sorts of uses for them.

Whether you’re seeing how many items are in a list, counting specific cases, or figuring out the number of unique values, Excel’s counting functions can help.

And when you combine them with other functions, COUNT, COUNTA, and COUNTIF can do some heavy lifting! Think about where you might put them to use in your own work, and you’ll certainly come up with some great uses.

2019-03-28T13:34:55+00:00