The direct answer: use =COUNTIF(range,"<>") to count non-empty cells in Excel. This counts every cell in the range that contains any value, including text, numbers, errors, and even formulas that return an empty string (“”). If you want to exclude cells with formulas that return empty text, use =SUMPRODUCT((range<>"")*(ISTEXT(range)=FALSE)) or switch to =COUNTA(range) which ignores truly blank cells but still counts formula blanks.
What “Not Empty” Actually Means in Excel
In Excel, a cell can be empty (truly blank, no formula, no value) or not empty (contains something). The distinction matters because a cell with a formula like =IF(A1="","",B1) is not truly blank—it contains a formula that returns an empty string. The COUNTIF function with the criteria "<>" treats that cell as not empty, because the cell contains a formula. In contrast, the COUNTA function also counts it, but COUNTBLANK does not count it because it sees the formula as a value.
For most purposes, “not empty” means “has any content,” and COUNTIF(range,"<>") is the simplest way to get that count. However, if you need to exclude cells with formulas that return empty text, you need a more advanced formula.
How to Count Non-Empty Cells: Step-by-Step
Assume you have a list of names in cells A2:A20, and you want to know how many cells are filled in.
- Click an empty cell where you want the result, say B2.
- Type the formula:
=COUNTIF(A2:A20,"<>") - Press Enter. The result shows the number of cells in A2:A20 that are not empty.
For example, if A2:A20 has 15 names and 4 blank cells, the result is 15.
To use the COUNTA alternative, type =COUNTA(A2:A20). This counts all non-blank cells, including those with formulas returning empty strings.
If you need to count non-empty cells that are not formulas returning empty text, use this array formula (press Ctrl+Shift+Enter in older Excel, but in Excel 365 you can just press Enter):
=SUMPRODUCT((A2:A20<>"")*(ISTEXT(A2:A20)=FALSE))
This counts cells that are not empty and are not text. But note that it also excludes numbers and dates? Actually, ISTEXT returns TRUE for text, so FALSE for numbers and dates. That formula counts cells that are not empty and are not text, meaning numbers, dates, errors, and logical values. If you want to count only truly non-empty cells (excluding formula blanks), a better approach is:
=SUMPRODUCT((A2:A20<>"")*(ISFORMULA(A2:A20)=FALSE))
But ISFORMULA requires Excel 2013 or later. In Excel 365, you can use =FILTER(A2:A20,A2:A20<>"") and then count with COUNTA, but that’s overkill.
COUNTIF vs COUNTA: Which Should You Use?
Both functions count non-empty cells, but they differ in how they handle cells with formulas that return empty strings.
- COUNTIF(range,"<>"): Counts any cell that contains any value, including formulas that return "". It does not count truly blank cells.
- COUNTA(range): Counts any cell that is not empty, including formulas that return "". It also counts cells with errors, logical values, and numbers. It ignores truly blank cells.
In practice, they give the same result for most datasets. The difference appears only when you have formulas that return empty text. For example, if A2 contains =IF(B2="","",B2) and B2 is blank, A2 shows nothing but is not empty. Both COUNTIF and COUNTA will count it. If you want to exclude such cells, you need a formula that checks if the cell is truly empty, like =SUMPRODUCT(--(A2:A20<>""),--(ISFORMULA(A2:A20)=FALSE)).
Common Problems and How to Fix Them
Problem 1: COUNTIF counts cells with spaces as not empty. A cell with a space character (" ") looks blank but is not. To ignore cells with only spaces, use =COUNTIF(range,"<> ") (with a space between the quotes) or use =SUMPRODUCT((TRIM(range)<>")*(range<>"")).
Problem 2: COUNTIF returns 0 for a range with visible values. This often happens when the cells contain formulas that return empty strings. The formula =COUNTIF(A2:A20,"<>") should count them, but if you used =COUNTIF(A2:A20,"<>""") incorrectly, it might not. Ensure you use the correct syntax: "<>".
Problem 3: Counting non-empty cells in a filtered list. COUNTIF ignores filters and counts all cells in the range. To count only visible non-empty cells, use =SUBTOTAL(3,A2:A20) for COUNTA, or =SUBTOTAL(103,A2:A20) for non-empty cells (103 ignores hidden rows).
Counting Non-Empty Cells with Conditions
Sometimes you need to count non-empty cells that also meet another condition. For example, count non-empty cells in column A where column B equals "Yes". Use COUNTIFS:
=COUNTIFS(A2:A20,"<>",B2:B20,"Yes")
This counts rows where A is not empty and B is "Yes".
For multiple conditions, add more pairs. For instance, count non-empty cells in A where B is "Yes" and C is greater than 10:
=COUNTIFS(A2:A20,"<>",B2:B20,"Yes",C2:C20,">10")
Alternatives to COUNTIF for Non-Empty Cells
- COUNTA: The simplest alternative. Use
=COUNTA(A2:A20). - SUBTOTAL: Use
=SUBTOTAL(103,A2:A20)to count non-empty cells in filtered lists. - SUMPRODUCT: Use
=SUMPRODUCT(--(A2:A20<>""))for more flexibility, especially when you need to exclude formula blanks. - PivotTable: Drag the field to the Values area and set it to Count, which counts non-empty cells.
Frequently Asked Questions
How do I count cells that are not blank in Excel? Use =COUNTIF(range,"<>") or =COUNTA(range). Both count non-empty cells, but COUNTA also counts errors and logical values.
Why does COUNTIF count cells with formulas that appear blank? Because the cell contains a formula, even if the formula returns an empty string. The cell is not truly empty. To exclude such cells, use =SUMPRODUCT((range<>"")*(ISFORMULA(range)=FALSE)).
Can I count non-empty cells in Google Sheets the same way? Yes, COUNTIF and COUNTA work identically in Google Sheets. The syntax is the same.
How do I count non-empty cells in a row instead of a column? Just change the range to a row, e.g., =COUNTIF(A1:F1,"<>").
Does COUNTIF count cells with only spaces? Yes, because a space is a character. To ignore spaces, use =COUNTIF(range,"<> ") (with a space) or trim the range first.
What if I want to count empty cells instead? Use =COUNTBLANK(range) or =COUNTIF(range,""). Note that COUNTIF with "" counts cells that are truly empty or contain empty string formulas.
Related
- Excel Template for Personal Expense Tracking
- Excel Template for Project Budget: How to Build One
- Excel Home Budget Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Calculate Percentage Change: ExamplesTo calculate percentage change in Excel, subtract the old value from the new value, then divide by the…
- Excel SUBTOTAL Formula With Filters: How It WorksTo total only the rows currently visible after filtering, use Excel’s SUBTOTAL function: if your amounts are in…
- Excel Filter Not Showing All Data? Find the Cause FastExcel filters usually miss rows because the filter range stops before the full dataset, or because the values…
- Excel Table Formulas Not Carrying Down? Fixes That WorkIf a formula in an Excel table is not carrying down, first confirm the data is an actual…
- C++ String Functions: Methods, Examples, and UsageC++ string functions are operations for creating, examining, searching, changing, and converting text. For most everyday work, use…
- Excel IF Formula Returns 0? Show a Blank Cell InsteadIf an Excel formula returns 0 and you want the cell to look blank, wrap the calculation in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.