COUNTA and COUNTBLANK in Excel
Last updated
COUNTA counts the cells that aren’t empty, whatever they contain: text, numbers, dates or errors. COUNTBLANK counts the empty cells of a range. For example, =COUNTA(B2:B11) counts the replies received and =COUNTBLANK(B2:B11) the replies still missing.
COUNTA and COUNTBLANK syntax
=COUNTA(value1, [value2], …) =COUNTBLANK(range)
- value1
- COUNTA: the first range or value to count, for example
B2:B11. - [value2], …
- COUNTA, optional: more ranges or values, for example
=COUNTA(B2:B11,D2:D11). - range
- COUNTBLANK: one range whose empty cells are counted, for example
B2:B11or a row such asB2:E2.
Practice COUNTA here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Count the replies received
Column B shows each guest’s reply to an invitation, and some guests haven’t answered yet. In E2, count how many replies you have received with COUNTA.
| Guest | Reply | |||
| Olivia Reed | Yes | Replies received | ||
| James Carter | No | |||
| Sofia Patel | ||||
| Liam Brooks | Yes | |||
| Emma Hughes | Yes | |||
| Noah Bennett | ||||
| Ava Collins | No | |||
| Ethan Ward | Yes | |||
| Mia Foster | ||||
| Lucas Gray | Yes |
How to use COUNTA and COUNTBLANK, step by step
- Click the cell where you want the count and type
=COUNTA(to count filled cells, or=COUNTBLANK(to count empty ones. - Select the range to count, for example the Reply column
B2:B11. - Close the parenthesis and press Enter.
- Check the result: on the same range, COUNTA plus COUNTBLANK equals the number of cells, unless some formulas return
"".
COUNTA and COUNTBLANK examples
Count replies received
=COUNTA(B2:B11)
Counts every reply, “Yes” or “No”: 7 of 10 guests have answered.
Count replies still missing
=COUNTBLANK(B2:B11)
Counts the empty cells: 3 guests to chase.
Share of tasks done
=COUNTA(C2:C11)/COUNTA(A2:A11)
Done dates divided by tasks: 6 of 10 gives 60% with a percentage format.
Missing fields on a row
=COUNTBLANK(B2:E2)
Counts the empty fields of one customer record; copy it down for every row.
Common COUNTA problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| COUNTA counts too many | A cell that looks empty holds a space, or a formula that returns "". Both count as filled for COUNTA. | Select the cell and press Delete to really empty it, or count with =COUNTIF(B2:B11,"?*") for text only. |
| Zeros and errors counted | COUNTA counts every value, including 0 and errors such as #N/A. | Use COUNT to count only numbers, or COUNTIF with a condition such as ">0". |
| COUNTBLANK and COUNTA overlap | A formula that returns "" counts as blank for COUNTBLANK and as filled for COUNTA. | Return 0 or a word instead of "", or keep in mind that both functions count those cells. |
| #NAME? | The function name is misspelled (=COUNTBLANKS) or written in another Excel language. | Type =COUN and pick COUNTA or COUNTBLANK from the list Excel suggests. |
COUNT vs COUNTA vs COUNTBLANK vs COUNTIF
COUNT counts only numbers (dates are numbers too). COUNTA counts every cell that isn’t empty: text, numbers, dates, logical values and errors. COUNTBLANK counts the empty cells, plus formulas that return "". COUNTIF counts the cells that meet a condition, for example =COUNTIF(B2:B11,"Yes") for the “Yes” replies only. On a column of text, COUNT returns 0: use COUNTA.
Frequently asked questions
What is the difference between COUNT and COUNTA?
COUNT counts only cells that hold numbers or dates. COUNTA counts every cell that isn’t empty, including text. On a list of names, COUNT returns 0 and COUNTA returns the number of names.
Why does COUNTA count an empty-looking cell?
The cell isn’t really empty: it may hold a space, an apostrophe or a formula that returns "". Select it and press Delete to clear it.
How do I count empty cells in Excel?
Use COUNTBLANK with one range: =COUNTBLANK(B2:B11). To count blanks in several ranges, add up several COUNTBLANK calls.
Can COUNTA count several ranges at once?
Yes. Separate them with commas: =COUNTA(B2:B11,D2:D11) adds up the filled cells of both ranges.
Keep practising
All 5 COUNTA and COUNTBLANK exercises, with explanations and progress tracking.
Open the COUNTA practice set