Conditional formula page
COUNTIF Nonblank Formula
Use COUNTIF with <> when you need to count filled cells in one range.
Count nonblank cells with COUNTIF.
The formula counts cells in A2:A100 that are not blank.
Copy formulas
=COUNTIF(A2:A100, "<>")=COUNTIF(A2:A100, "<>")COUNTIF with <> counts cells that are not blank, including cells containing spaces; cells returning an empty string should be tested in the target spreadsheet.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
The formula counts cells in A2:A100 that are not blank.
How the formula works
- A2:A100 is the range being checked.
- The <> criteria means not equal to blank.
- This is useful before cleanup or import checks.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100 | The range whose nonblank cells are counted. |
| <> | The COUNTIF criterion meaning not equal to blank. |
| count result | The number of cells that meet the nonblank criterion. |
Verified examples
=COUNTIF(A2:A100, "<>")Excel: Use the sample Email column containing three nonblank emails and one blank row. Returns: 3
=COUNTIF(B2:B5,"<>")Google Sheets: Enter four values in B2:B5. Returns: 4
=COUNTIF(C2:C5,"<>")Excel: Enter a space in each of C2:C5. Returns: 4
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A visually empty cell is counted | The cell contains spaces or another value that is not an actual blank. | Clean the source or use a helper condition that explicitly tests the required content. |
| The count is larger than expected | The selected range includes headers, notes, or an oversized unused area with values. | Limit the range to the intended data rows. |
| Formula-generated blanks behave unexpectedly | Different spreadsheet engines can treat formulas returning empty strings differently from truly empty cells. | Test the exact workbook and use a helper column when empty-string behavior matters. |
When not to use this formula
- Do not use COUNTIF(A2:A100, "<>") when spaces should be treated as blank; clean or normalize the source first.
Alternatives
| Alternative | When to use it |
|---|---|
| Count Blank Cells Formula | Use when the report needs the number of blank cells instead. |
| Remove Blank Rows Formula | Use when the goal is a filtered row view rather than a count. |
Related formulas
Official references
- Use the COUNTIF function in Microsoft Excel from Microsoft
FAQ
Do spaces count as nonblank?
Yes. A cell containing a space is not truly blank, so COUNTIF with <> counts it.
How should formula-generated empty strings be handled?
Test the workbook's behavior and use a cleaned helper condition when formula results that look blank must be excluded.