Use =COUNTA(range) to count every cell that is not empty in a range. For a more controlled count that excludes cells containing only spaces or formula blanks, use =COUNTIF(range,"<>"&"") or =SUMPRODUCT(--(range<>"")).
Why the Simple Answer Isn’t Always Enough
In Excel, “empty” can mean several things. A cell can look blank but actually contain a space character, a formula that returns "", or a zero-length text string. COUNTA counts cells that contain any value, including spaces and formula results that appear blank. If you need to count only cells that are truly non-empty (no spaces, no invisible characters), you need a different approach.
For example, if A1 contains =IF(1>2,"","text") it returns “text”, so it’s counted. But if A1 contains =IF(1>2,"","") it returns an empty string, which COUNTA still counts because the cell contains a formula. To exclude those, use =COUNTIF(A1:A10,"?*") which counts cells that contain at least one character (excluding empty strings and truly blank cells).
How COUNTA Works and Its Limitations
COUNTA counts all cells that are not empty, including numbers, text, dates, errors, and logical values (TRUE/FALSE). It does not count cells that are truly blank. The syntax is simple: =COUNTA(value1, [value2], ...) or a range like =COUNTA(A1:A10).
Limitations: It counts cells with spaces (e.g., a cell containing a single space from a user typo). It also counts cells with formulas that return an empty string. If you have a column where some cells contain "" from formulas, COUNTA will overcount.
Example: In B2:B6, cells contain: “Apple”, “”, ” “, 123, TRUE. =COUNTA(B2:B6) returns 5 because it counts the space and the empty string. To get the count of cells with actual visible content, use =COUNTIF(B2:B6,"?*") which returns 3 (Apple, 123, TRUE).
Using COUNTIF to Exclude Blanks and Spaces
The COUNTIF function with criteria "<>"&"" counts cells that are not equal to an empty string. This excludes truly blank cells and cells with empty strings, but still includes spaces. For a stricter count, use "?*" which counts cells that contain at least one character (so spaces are excluded because a space is a character, but it’s not a letter or number? Actually, a space is a character, so "?*" counts cells with spaces too). To exclude spaces, you need a more complex formula.
To count cells that are not empty and do not contain only spaces, use =SUMPRODUCT(--(TRIM(range)<>"/")) but that’s not standard. A better approach is =COUNTIF(range,"<>"&" ") but that only excludes a single space, not multiple spaces. The most robust way is to use =SUMPRODUCT(--(LEN(TRIM(range))>0)) which counts cells where the trimmed length is greater than zero.
Example: With C1:C5 containing “text”, ” “, “”, “more”, ” “, =SUMPRODUCT(--(LEN(TRIM(C1:C5))>0)) returns 2 (text and more).
Counting Non-Empty Cells in a Filtered List
If you have a filtered range and want to count only visible non-empty cells, use the SUBTOTAL function. =SUBTOTAL(3, range) counts visible cells that are not empty (using COUNTA behavior). The function number 3 is for COUNTA, and 103 for ignoring hidden rows manually. For example, =SUBTOTAL(3, A2:A100) will count only visible non-empty cells after filtering.
Note: SUBTOTAL ignores rows hidden by a filter, but not rows hidden manually (right-click > Hide). To count visible cells regardless of how they are hidden, use 103: =SUBTOTAL(103, A2:A100).
Counting Non-Empty Cells Across Multiple Columns or Sheets
To count non-empty cells across multiple ranges, you can sum multiple COUNTA results: =COUNTA(A2:A10)+COUNTA(C2:C10). For a 3D reference across sheets (e.g., Sheet1:Sheet3!A2:A10), use =SUMPRODUCT(COUNTA(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!A2:A10"))) but that’s complex. Simpler: =COUNTA(Sheet1:Sheet3!A2:A10) works in Excel 365 and 2021, but it counts non-empty cells across all sheets in the range. However, it doesn’t exclude spaces or empty strings.
For a more robust multi-sheet count, you can use a helper column or a VBA macro, but for most users, a simple sum of multiple COUNTA functions is sufficient.
Common Problems and Misconceptions
Misconception: COUNTA counts only cells with text. It counts numbers, dates, errors, and logical values too.
Problem: Counting cells with formulas that return “” – Solution: Use =COUNTIF(range,"?*") or =SUMPRODUCT(--(LEN(TRIM(range))>0)).
Problem: Counting cells with spaces – Solution: Use the TRIM approach or a helper column that trims and then counts.
Problem: Counting in a filtered list – Use SUBTOTAL.
Problem: Counting cells that are not blank but contain only spaces from imported data – Use =COUNTIF(range,"<>"&" ") but that only excludes a single space. Better to use the TRIM method.
Comparison: COUNTA vs. COUNT vs. COUNTBLANK
- COUNTA – Counts all non-empty cells (including spaces and empty strings).
- COUNT – Counts only numeric cells, ignoring text and blanks.
- COUNTBLANK – Counts truly empty cells, but also counts cells with empty strings? Actually, COUNTBLANK counts cells that are empty, including cells with formulas that return “”? In Excel, COUNTBLANK counts cells that are truly blank and also cells that contain empty strings from formulas? Let’s check: In Excel, a cell with formula =”” is considered blank for COUNTBLANK? Yes, COUNTBLANK counts cells that are empty, including those with formulas returning “”. So if you want to count truly empty cells excluding those with empty strings, you need a different approach.
For a quick comparison: =COUNT(A1:A10) counts numbers, =COUNTA(A1:A10) counts non-empty, =COUNTBLANK(A1:A10) counts blanks (including empty strings).
Frequently Asked Questions
How do I count non-empty cells in Google Sheets? Use the same functions: =COUNTA(range) works, but note that Google Sheets treats empty strings from formulas as empty? Actually, in Google Sheets, COUNTA also counts cells with formulas that return empty strings. To exclude them, use =COUNTIF(range,"?*") or =SUMPRODUCT(--(LEN(TRIM(range))>0)).
Why does COUNTA count a cell with a space? Because a space is a character, so the cell is not empty. To exclude spaces, use the TRIM method.
Can I count non-empty cells based on a condition? Yes, use COUNTIFS. For example, count non-empty cells in column A where column B equals “Yes”: =COUNTIFS(B2:B100,"Yes",A2:A100,"<>"&"").
How do I count non-empty cells in a row? Use =COUNTA(2:2) to count all non-empty cells in row 2.
Does COUNTA count errors? Yes, errors like #N/A are counted as non-empty.
What is the fastest way to count non-empty cells in a huge range? Use =COUNTA(A:A) for an entire column, but be careful with whole-column references as they can slow down calculation. Better to limit the range.
Related
- Excel Templates for Time Tracking: Best Free Options
- Profit and Loss Excel Templates: How to Use Them
- Excel Template Sales Tracker: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Table Name Already Exists Error: How to Fix ItThe Excel table name already exists error means the name you entered is already assigned to another table…
- Excel Not Reading Dates as Dates? Fix the Cell ValuesIf Excel is not reading a date as a date, the cell usually contains text that only looks…
- Excel Not Allowing Insert Row? How to Fix ItIf Excel won’t let you insert a row, first check whether the sheet is protected, then check whether…
- Why Some Excel Dates Are Left Aligned and How to Fix ThemIn Excel, dates that are left aligned are often text rather than real date values: Excel normally aligns…
- VLOOKUP Not Working? Fix Common Excel Lookup ErrorsIf VLOOKUP is not working in Excel, first set its final argument to FALSE for an exact match,…
- Standard Error in Excel: How to Calculate ItTo find the standard error of the mean in Excel, divide the sample standard deviation by the square…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.