Countif Not Blank in Excel: Formula & Fixes

Formulas & functions · Updated November 22, 2026

The simplest way to count non-blank cells with COUNTIF is =COUNTIF(range,"<>"&"") — the criteria "<>" means “not equal to nothing,” so it counts every cell that contains any value, including text, numbers, errors, and even formulas that return an empty string. For a more robust option that ignores formula blanks, use =COUNTA(range), but note that COUNTA counts cells with formulas that return “” (empty text) as non-blank, while =COUNTIF(range,"?*") counts only cells with at least one character.

Why “Not Blank” Is Trickier Than It Sounds

In Excel, a cell can look empty but actually contain a formula that returns an empty string (""), a space, or an apostrophe. These are technically non-blank to functions like COUNTA, but they appear blank to the eye. This distinction matters when you’re tallying entries in a column: a formula that pulls from another sheet might leave “” in a cell, and a simple COUNTA will overcount. The COUNTIF approach with "<>" treats those as non-blank as well, because the cell contains a formula result. To count only cells with visible text or numbers, you need =COUNTIF(range,"?*"), which counts cells with at least one character (the ? matches any single character, * matches any sequence).

The Core Formulas: COUNTIF, COUNTA, and Their Differences

Here are the three main ways to count non-blank cells, with their exact behavior:

  • =COUNTIF(range,"<>") — Counts cells that are not truly empty (no formula, no space). This is the classic “not blank” count.
  • =COUNTA(range) — Counts cells that contain any value, including errors, text, numbers, and formula results like "". If a formula returns an empty string, COUNTA counts it.
  • =COUNTIF(range,"?*") — Counts cells that contain at least one character (text or a number formatted as text). It ignores truly empty cells and cells with "" from formulas, but it also ignores cells with only a space (a space is a character, so it would count — but a truly blank cell won’t).

For example, with data in A1:A5 where A1 has “Apple”, A2 is empty, A3 has a formula returning “”, A4 has 0, and A5 has a space: =COUNTIF(A1:A5,"<>") returns 4 (A1, A3, A4, A5 — A3 is counted because the formula result is not truly blank), =COUNTA(A1:A5) also returns 4 (same reason), and =COUNTIF(A1:A5,"?*") returns 3 (A1, A4, A5 — A4’s 0 is a number, but does it count? Actually, ?* counts text and numbers that are formatted as text; a numeric 0 is not counted because it’s not a text string. So it would return 2 if A4 is numeric. Let me clarify: ?* only counts text entries, not numbers. So it would count A1 and A5, returning 2. For numbers, use <>).

How to Use COUNTIF for Non-Blank Cells (Step-by-Step)

Assume you have a list of sales in B2:B100, and you want to count how many cells have a value (not blank).

  1. Click on an empty cell, say D2.
  2. Type =COUNTIF(B2:B100,"<>")
  3. Press Enter. The cell returns the count of non-blank cells in that range.

If you want to count non-blank cells that meet another condition, say only cells with a value greater than 10, use =COUNTIFS(B2:B100,">10",B2:B100,"<>"). The <> criteria can be combined with other criteria in COUNTIFS.

Common Pitfalls and How to Avoid Them

One frequent issue is that =COUNTIF(range,"<>") counts cells that contain formulas returning "" as non-blank. To exclude those, you need a different approach, such as =SUMPRODUCT((range<>"")*(ISFORMULA(range)=FALSE)) — but that's complex. A simpler workaround is to use =COUNTIF(range,"?*") if you only want text entries, or use =SUMPRODUCT(--(LEN(TRIM(range))>0)) to count cells that have at least one non-space character (this ignores spaces and empty strings). Another pitfall: using <> with a cell reference, like =COUNTIF(A1:A10,"<>"&B1), counts cells not equal to the value in B1, not non-blank. To count non-blank, always use "<>" alone or with "".

Alternatives: SUMPRODUCT and Dynamic Arrays

For more control, =SUMPRODUCT(--(A1:A100<>"")) works similarly to COUNTIF but is an array formula that can be extended. For example, to count non-blank cells in column A that are also numbers, use =SUMPRODUCT((A1:A100<>"")*ISNUMBER(A1:A100)). In Excel 365, you can use the FILTER function: =ROWS(FILTER(A1:A100,A1:A100<>"")) returns the count of non-blank cells. This is especially useful when you want to spill the actual non-blank values elsewhere.

Counting Non-Blank in Google Sheets vs. Excel

Google Sheets uses the same functions: =COUNTIF(A1:A10,"<>") and =COUNTA(A1:A10) work identically. However, Google Sheets has a dedicated =COUNTIF(A1:A10,"<>") that behaves the same. The main difference is that Google Sheets treats "" from formulas as blank in some contexts? Actually, no — it treats it as non-blank for COUNTA. So the same pitfalls apply. In Excel for Mac, the formulas are the same; no difference in syntax. For Excel on the web, same. So you can use the same formulas everywhere.

Frequently Asked Questions

Why does my COUNTIF return 0 when I have visible text? Check if the cells contain leading apostrophes or spaces. Use =TRIM(A1) to see. Also, ensure the range is correct and you haven't accidentally used "<>" with a value, like "<>0", which counts cells not equal to 0, not non-blank.

Does COUNTIF count cells with formulas that return blank? Yes, if the formula returns an empty string "", COUNTIF with "<>" counts it as non-blank. To exclude those, use =COUNTIF(range,"?*") for text only, or use SUMPRODUCT with ISFORMULA.

What's the difference between COUNTA and COUNTIF(range,"<>")? COUNTA counts all non-empty cells, including errors and formula results. COUNTIF with "<>" also counts those, so they are essentially the same for non-blank counts. The difference is that COUNTA is simpler and faster, but COUNTIF allows adding other criteria.

How do I count non-blank cells in a filtered list? Use the SUBTOTAL function: =SUBTOTAL(103,A1:A100) counts visible non-blank cells. The 103 function code counts non-blank cells in filtered data, ignoring hidden rows.

Can I count non-blank cells with multiple conditions? Yes, use COUNTIFS: =COUNTIFS(A1:A100,"<>",B1:B100,"Yes") counts rows where column A is non-blank and column B is "Yes".

Why does =COUNTIF(A1:A10,"<>") count cells with spaces? Because a space is a character, so the cell is not truly blank. To ignore cells with only spaces, use =SUMPRODUCT((TRIM(A1:A10)<>")*1) or =COUNTIF(A1:A10,"?*") but note that ?* counts spaces as characters too? Actually, ?* matches any single character followed by any sequence, so a space would match. To exclude spaces, use =SUMPRODUCT((TRIM(A1:A10)<>")*1).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.