Use COUNTA to count cells that aren’t blank

Suppose you need to know if your team members have entered all their project hours in a worksheet. In other words, you need to count the cells that have data. And to complicate matters, the data may not be numeric. Some of your team members may have entered placeholder values such as TBD, as shown in this example:

CountA function with a range selection

The COUNTA function counts only the cells that have data, but be aware that "data" can include spaces, which you can't see. And yes, you could probably count the blanks in this example yourself, but imagine doing that in a big workbook.

Here’s how you use this function:

  1. Determine the range of cells you want to count. The example above used cells B2 through D6.

  2. Select the cell where you want to see the result, the actual count. Let's call that the result cell.

  3. In either the result cell or the formula bar, type the formula and press Enter, like so:


You can also count the cells in more than one range. This example counts cells in B2 through D6, and in B9 through D13:

COUNTA function with 2 ranges selected

If you know you don't need to count text data, just numbers and dates, use the COUNT function.

More ways to count cells that have data

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.