The Excel COUNT function, explained interactively.
COUNT returns the number of numeric cells in a range. Text, booleans, and blanks are silently skipped — making COUNT a quick diagnostic for “how many real numbers are actually in this column”. For “how many cells have any value”, reach for COUNTA; for “how many cells match a condition”, COUNTIF and COUNTIFS.
How to use COUNT
- Type
=COUNT(and select the range to measure — usually a whole column (B:B) or a bounded block (B2:B100). - Close the parenthesis. COUNT returns a single integer — the tally of numeric cells in that range.
- To compare with total non-blanks, put a
=COUNTA(B2:B100)in an adjacent cell. If the two differ, some rows have text, booleans, or formula-blanks you might not have noticed. - For conditional counts, switch to COUNTIF / COUNTIFS. COUNT alone has no filter.
The demo below deliberately mixes numeric scores with text, a boolean, and one blank. The “Type” column shows what each cell contains; a ✓ marks the rows COUNT actually adds to its tally.
"2026-04-15") do not.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Field | Value | Type | Count |
| 2 | Score | 92 | number ✓ | 3 |
| 3 | Score | 87.5 | number ✓ | |
| 4 | Submitted | 2026-04-15 | text | |
| 5 | Passed? | TRUE | bool | |
| 6 | Notes | Great | text | |
| 7 | Score | (blank) | blank | |
| 8 | Score | 74 | number ✓ | |
| 9 | Notes | N/A | text |
=COUNT(B2:B9). COUNT returns 3 — only the numeric cells are counted. Text ("Great", "N/A", the text-stored date), the boolean TRUE, and the one blank cell are all skipped. Compare with COUNTA, which would return 7 (everything non-blank).COUNT syntax and arguments
Variadic — one required argument, up to 254 optional. See Microsoft’s official COUNT reference for the canonical specification.
COUNT examples
Four patterns that cover most real-world COUNT use.
Example 1: COUNT — tally numeric responses
The canonical case. Count how many rows in a numeric column actually contain numbers.
Returns 68 for a survey where 68 of 99 rows have numeric scores and the rest are empty or text (“N/A”). Adjacent =COUNTA(B2:B100) would return the full count of non-blank rows, making the gap visible.
Example 2: COUNT — diagnose text-stored numbers
The top gotcha with imported data. Compare COUNT with COUNTA (or ROWS) to spot columns whose numbers are secretly text.
If the result is zero, every non-blank cell is a real number. If positive, that many cells contain text, booleans, or formula-blanks hiding inside. Same diagnostic whether it’s leading whitespace, a stray apostrophe, or a CSV-imported column stuck in text format.
Example 3: COUNT — multiple ranges in one call
Like other aggregate functions, COUNT takes up to 255 arguments. Useful for tallying across non-contiguous blocks or sheets.
Sums the numeric-cell counts from three monthly sheets in a single formula. Combine with SUM over the same ranges to compute a total — together COUNT and SUM give you the average denominator and numerator for quarterly roll-ups.
Example 4: COUNT for a dynamic range size
Use COUNT to feed other formulas that need a row count — OFFSET arguments, dynamic chart sources, SUMPRODUCT divisors.
The two are equivalent as long as every cell is numeric. If B2:B100 contains text placeholders, AVERAGE automatically skips them; a manual SUM / COUNT does too because COUNT skips the same cells. Useful when building conditional averages by hand with SUMPRODUCT.
Common COUNT errors and fixes
Four failure modes, each with what to check and how to recover.
COUNT returns 0 when cells look numeric
Cause: the “numbers” are stored as text. Left-aligned cells with a small green triangle in the corner are the telltale sign. COUNT skips them because their data type is text, even though they read as digits.
Select the range → Data → Text to Columns → Finish. Or put =B2*1 in a helper column. Or =VALUE(B2) for the semantic-clear version. All three coerce text-digits into real numbers that COUNT will tally.
COUNT over-counts by 1 after a formula refactor
Cause: a cell that looks blank actually contains ="" — an empty string returned by a formula. COUNTA counts it as non-blank, but COUNT skips it because "" is text.
If you see COUNTA and COUNT disagree, inspect the “blank” cells: they may contain whitespace formulas. Replace with IF(…, …, ) (bare comma, no quotes) to return a true blank, or rewrite upstream formulas to surface actual nulls.
COUNT counts a cell that should be skipped
Cause: a boolean stored as a number. TRUE and FALSE in argument lists (not ranges) are counted by COUNT. In a range, boolean cells are skipped; passed directly as arguments, they’re coerced to 1 / 0 and counted.
This is a documented Excel quirk. If it matters, keep booleans inside ranges rather than passing them as direct arguments. Or wrap with ISNUMBER and use SUMPRODUCT for the strictest behaviour.
COUNT result doesn’t include dates
Cause: the dates are stored as text (left-aligned, with dashes and no underlying serial number). Real Excel dates are counted; text that looks like a date is not.
Convert with Data → Text to Columns and pick Date format in step 3. Or wrap with DATEVALUE(B2) in a helper column. After conversion the cells right-align and COUNT picks them up.
COUNT vs COUNTA, COUNTIF & COUNTBLANK
Four functions count. Pick by what counts as “a cell worth counting” in your context.
| Function | Counts | Conditional? | Typical use |
|---|---|---|---|
| COUNT | Numeric cells only | No | Tally responses in a numeric column |
| COUNTA | All non-blank cells | No | Tally any-type responses, row-exists count |
| COUNTIF | Cells matching a criterion | One condition | How many rows equal “East”, > 100, contain “AB” |
| COUNTBLANK | Empty cells only | No | Find missing data in a form / survey column |
Rule of thumb: COUNT for numeric columns where you want to know how many real numbers you have; COUNTA for “how many rows exist” in any column; COUNTIF / COUNTIFS for conditional counts; COUNTBLANK to find gaps. Comparing COUNT and COUNTA in the same sheet is a reliable diagnostic for data quality problems.
COUNT frequently asked questions
6.01What’s the difference between COUNT and COUNTA?▸
COUNT counts only numeric cells — numbers, dates (which are numbers under the hood), and booleans if they’re actual booleans. COUNTA counts every non-blank cell regardless of type: numbers, text, booleans, and errors. Use COUNT when the column is supposed to be numeric; use COUNTA when you want a row-exists count, e.g. how many people responded.
6.02Does COUNT include dates?▸
Yes, if the date is a real Excel date (right-aligned, stored as a serial number). COUNT treats dates as numbers. Text-stored dates like "2026-04-15" — which left-align in the cell — are skipped. The Format Cells dialog tells you which kind you have; Data → Text to Columns converts text dates to real dates in one pass.
6.03Why is COUNT returning 0 when my column has numbers?▸
Because the numbers are stored as text. A number stored as text left-aligns in the cell and often shows a small green triangle in the corner. Select the range and use Data → Text to Columns → Finish, multiply by 1 in a helper column, or wrap with VALUE() to force conversion. This is the same gotcha that makes SUM show 0.
6.04Does COUNT skip blank cells?▸
Yes — blank cells are silently skipped. So are cells containing only a formula that returns an empty string (=""), even though those look blank. Watch for the latter: COUNTA counts those as non-blank, so a mismatch between COUNT and COUNTA flags columns that have hidden "" results.
6.05Can COUNT count cells that match a condition?▸
No — COUNT is unconditional. Use COUNTIF for one condition and COUNTIFS for multiple. COUNTIF(range, criteria) counts cells in range matching criteria; COUNTIFS takes pairs of criteria_range / criteria and ANDs them. For boolean expressions COUNTIF can’t handle, SUMPRODUCT with a logical array is the classic fallback.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.