Wednesday, December 15, 2021

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". For that, use the COUNTA function.

Here's an example:

The COUNTA function

The 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. So, to use the formula:

  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:

    =COUNTA(B2:B6)

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

Using COUNTA to count 2 cell ranges

You can see Excel highlights the cells ranges, and when you press Enter, the result appears:

COUNTA function results

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

No comments:

Post a Comment