FormuladrillCOUNT, COUNTA and COUNTBLANK
COUNT(range) counts cells that hold numbers (including dates), COUNTA(range) counts every cell that is not empty (numbers, text, errors, even a formula returning ""), and COUNTBLANK(range) counts cells that are empty or hold an empty string. Pick the one that matches what you mean by filled.
Syntax
COUNT(value1, [value2], ...) COUNTA(value1, [value2], ...) COUNTBLANK(range)| Part | What it is |
|---|---|
value1, value2... | Ranges or values to count. COUNT and COUNTA accept up to 255 arguments. |
range (COUNTBLANK) | One range to check for empty cells. |
Examples
=COUNT(A2:A100)Result: The number of cells in A2:A100 that contain a number
Text and blanks are not counted; a date counts because it is a number.
=COUNTA(A2:A100)Result: The number of cells that contain anything at all
=COUNTBLANK(A2:A100)Result: The number of empty cells
=COUNTA(A2:A100) - COUNT(A2:A100)Result: How many cells hold text, which catches numbers stored as text
Common mistakes
- COUNTA counts cells with a formula that returns "" as filled, and COUNTBLANK counts the same cells as blank, so the two can add up to more than the range size.
- COUNT returns fewer than expected: some numbers are stored as text (green triangle). Convert them with VALUE or Text to Columns.
- COUNT counts dates and TRUE/FALSE typed directly as arguments but not TRUE/FALSE in cells.
- A cell with a single space is counted by COUNTA and not by COUNTBLANK; use TRIM or Find and Replace to clean it.
Practice COUNT, COUNTA and COUNTBLANK
These drills check your answer on live data, free, no account needed.
- Count the answersSome people left the feedback column empty. Count how many people did write feedback, and put the answer in E2.
- Count the numbersCount how many days have a visitor number recorded. Blank cells and the word closed must not be counted. Put the answer in E2.
Questions
What is the difference between COUNT and COUNTA?
COUNT counts only numeric cells. COUNTA counts any non-empty cell, including text, so COUNTA is the one for counting entries in a list of names.
How do I count non-blank cells in Excel?
=COUNTA(range), or =COUNTIF(range, "<>"). Both treat an empty-string formula result as filled.
How do I count cells with text only?
=COUNTIF(range, "*") counts cells containing text, or =SUMPRODUCT(--ISTEXT(range)).
Related
- COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
- COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
- ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.