COUNT, COUNTA and COUNTBLANK: Counting Cells in Excel

The Excel COUNT function counts cells that hold numbers. Its two partners count other things: COUNTA counts every filled cell, and COUNTBLANK counts the empty ones. Together they are the fastest way to check a list before you trust it, which is exactly what employers want to see in a data test.

What do COUNT, COUNTA and COUNTBLANK count?#

Each function counts a different kind of cell. They all take a range and return a number.

COUNT and COUNTA accept several ranges separated by commas. COUNTBLANK takes a single range. For a side-by-side comparison of all three, read COUNT vs COUNTA vs COUNTBLANK.

What each function counts: the tricky cases#

The table shows how each function treats contents that often cause confusion. "Yes" means the cell is counted.

Zero is a number, so it is never "blank". If you see a 0 in a list of scores, COUNTBLANK won't flag it.

A worked example: checking applicant data#

Here is a list of job applicants. One score was typed as a word, one is missing, and one status is missing.

COUNT gives 4 because "absent" is text. COUNTA gives 5 because "absent" is still something. Start each range at row 2, so the header isn't counted.

How to use the Excel COUNT function#

COUNT tells you how many cells in a range hold real numbers. Type =COUNT(, select the range, type ) and press Enter.

Use it when the column should contain only numbers: amounts, scores, quantities or dates. Excel stores dates as numbers, so COUNT includes them. (Learn why in why Excel stores dates as numbers.)

COUNT is also how you spot numbers stored as text. These look like numbers, but Excel treats them as words, often after copying from another system. If a column has six amounts and COUNT returns 5, one of them is text. SUM and AVERAGE skip it too, so your totals are quietly wrong.

How to use COUNTA#

COUNTA counts every cell that isn't empty, whatever it contains. Use it on text columns such as names, statuses or email addresses, where COUNT would return 0.

COUNTA counts more than you might expect:

• Text and numbers, including a single space typed by mistake.
• Error values such as #N/A.
• Formulas that return empty text, like =IF(B2>80,"Pass",""). The cell looks blank, but it contains a formula, so COUNTA counts it.

If COUNTA seems too high, look for these "invisible" values. The COUNTA function guide has more examples.

How to use COUNTBLANK#

COUNTBLANK counts the empty cells in one range. =COUNTBLANK(C2:C7) returns 1, the missing status for H. Lee.

COUNTBLANK and COUNTA don't always add up to the number of rows. A formula that returns "" is counted by both: COUNTA sees a formula, and COUNTBLANK sees an empty result. A cell with a space is counted only by COUNTA.

A useful follow-up is a completion rate: what share of the rows are filled in.

=COUNTA(C2:C7)/ROWS(C2:C7)

ROWS returns how many rows the range has (6), so the result is 5 ÷ 6 = 0.833, or 83% once you format the cell as a percentage. The same idea is behind the percent change formula: one number divided by another, shown as a percent. To display a clean 83% instead of 83.3333%, see how to round numbers in Excel.

Find text hiding in a number column#

Subtract COUNT from COUNTA on the same range. The difference is the number of filled cells that aren't numbers.

=COUNTA(B2:B7)-COUNT(B2:B7)

On the applicant data this returns 1: M. Silva's "absent". On a clean column it returns 0. This one-line check works on any list of amounts, and it catches numbers stored as text before they break a total. If you copy the check across several columns, keep each range relative, or lock it with $ signs when it must stay fixed. Absolute references and the $ sign explains how.

When to use COUNTIF instead#

COUNT, COUNTA and COUNTBLANK count by type of content. They can't count by condition, such as "status is Interview" or "score above 80". For that, use COUNTIF with one condition or COUNTIFS with several:

=COUNTIF(C2:C7,"Interview")

This returns 2. Learn the criteria rules in how to use COUNTIF in Excel.

Common counting mistakes#

• Using COUNT on text. =COUNT(C2:C7) on the status column returns 0. Text needs COUNTA.
• Including the header row. =COUNTA(B1:B7) counts "Test score" as a value and returns 6.
• Trusting a blank-looking cell. Spaces and "" formulas look empty but count in COUNTA.
• Confusing COUNT with SUM. COUNT tells you how many numbers there are, not what they add up to.

Practise it#

Practise COUNT, COUNTA and COUNTBLANK in a live spreadsheet: you'll audit the applicant list above, count missing statuses and build the text check in one formula. Then try the survey exercise on this page.