**The Excel-Function COUNT Explained:**

Count Values in Cells

Count Values in Cells

*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.

**Table of Contents**

**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!

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

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.

Finally, close off the parentheses and hit Enter.

It doesn’t get much simpler than that!

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.

Now close off the parentheses and hit Enter.

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.

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

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**.

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)**

**range**is where Excel will look for cells that meet the criterion.

**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.

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

Select column B for the range:

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

Close off the formula and hit **Enter**.

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.

**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.

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.

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!

**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.