COUNTIF Not Blank in Excel: Formula and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

To count non-empty cells in Excel with COUNTIF, use the criterion "<>". The formula =COUNTIF(B2:B11,"<>") counts every cell in the range that is not blank. This is the standard way to run a countif not blank check, and it works in every modern version of Excel.

Quick Answer

The not-blank criterion for COUNTIF is the string "<>", written with double quotes: =COUNTIF(range,"<>").
=COUNTIF(B2:B11,"<>") returns 8 for a range of 10 cells that contains 2 truly empty cells.
COUNTA does the same job with simpler syntax: =COUNTA(B2:B11) also returns 8 [1].
Both functions also count formulas that return "" as non-blank, while COUNTBLANK counts those same cells as blank, so the choice matters [1][2].
For multiple conditions, switch to COUNTIFS, which accepts up to 127 range and criteria pairs [3].

Syntax

COUNTIF takes two arguments. The second one carries the not-blank test.

Argument	Required?	Meaning
range	Required	The cells you want to evaluate.
criteria	Required	The condition each cell must meet. For not blank, use `"<>"`.

The full pattern is:

$$=\text{COUNTIF}(\text{range},\;"<>")$$

COUNTIFS extends this to several ranges and criteria at once [3]:

$$=\text{COUNTIFS}(\text{range}_1,\text{criteria}_1,\;\text{range}_2,\text{criteria}_2,\;\dots)$$

How It Works

The criterion "<>" means "not equal to nothing." Excel evaluates each cell in the range against that test. A cell holding text, a number, a date, a logical value, or an error passes the test and is counted. A cell that is genuinely empty fails it and is skipped.

This is the same logic behind the family of counting functions Excel provides. COUNTA counts cells that are not empty, COUNT counts only numeric cells, COUNTBLANK counts empty cells, and COUNTIF counts cells meeting a criterion you supply [4]. When your criterion is "not blank," COUNTIF and COUNTA converge on the same answer for ordinary data.

The divergence appears with formulas. A formula that returns an empty string, written as "", is not an empty cell. COUNTA counts it because the cell contains a formula result [1]. COUNTBLANK also counts it, because the displayed value is empty text [2]. COUNTIF with "<>" counts it too, because the cell is not truly empty. To skip such cells, use =SUMPRODUCT(--(LEN(range)>0)) instead. If your range contains formulas that can return "", decide which behavior you actually want before picking a function.

Worked Example

A short survey table records ten respondents and their answers. Two respondents left the answer field empty.

Row	A (Respondent)	B (Answer)	C (Non-Blank Count)	D (COUNTA Check)
1	Respondent	Answer	Non-Blank Count	COUNTA Check
2	R1	Yes	`=COUNTIF(B2:B11,"<>")` -> displays 8	`=COUNTA(B2:B11)` -> displays 8
3	R2	No
4	R3	Yes
5	R4
6	R5	Maybe
7	R6	Yes
8	R7
9	R8	No
10	R9	Yes
11	R10	Yes

Cell C2 holds the COUNTIF formula and returns 8. Cell D2 holds COUNTA and also returns 8. Both functions agree because the two empty cells in B5 and B8 are truly empty, with nothing typed in them and no formula behind them.

The 8 counted cells are B2, B3, B4, B6, B7, B9, B10, and B11. The 2 skipped cells are B5 and B8. If you want to see the underlying logic of COUNTIF before adding the not-blank criterion, the COUNTIF function guide walks through the syntax from the ground up.

More Examples

Count non-blank entries in a whole column region. Point the range at the block of data, not the entire column, so header text does not inflate the result:

=COUNTIF(C2:C500,"<>")

Count non-blank text answers only. If you want to exclude numbers and count only text entries, combine the not-blank test with a wildcard:

=COUNTIF(B2:B11,"?*")

The ?* pattern requires at least one character, so it counts text and skips numbers and empty cells. For a deeper look at text matching, see COUNTIF cell contains text.

Count non-blank answers within one region. COUNTIFS lets you add a second condition, such as a region column:

=COUNTIFS(B2:B11,"<>",A2:A11,"North")

This counts rows where the answer is present and the region is North. The SUMIF and SUMIFS guide covers the same multi-criteria pattern for adding values instead of counting them.

Count non-blank numeric entries. When every valid entry is a number, COUNT is the tighter choice because it ignores text and blanks entirely. The COUNT function guide explains where it fits.

Count non-blank cells across two separate ranges. COUNTA accepts up to 255 arguments, so you can pass several ranges in one formula [1]:

=COUNTA(B2:B11,D2:D11)

Errors and How to Fix Them

#VALUE! from a closed workbook. COUNTIF and COUNTIFS return #VALUE! when the formula points at a cell or range in a workbook that is closed. Open the linked workbook and press F9 to refresh [5].

#VALUE! from an overlong string. A criterion string longer than 255 characters triggers the error. Shorten it, or split it with the ampersand operator, as in =COUNTIF(B2:B12,"long string"&"another long string") [5].

Cells that look empty but are counted. Check whether the cells contain spaces. A cell holding a single space looks empty on screen but counts as data [6]. Use TRIM or Find and Replace to remove stray spaces.

A result that is too high. Header rows, totals rows, and helper columns inside the range all count as non-blank. Shrink the range to the data rows only.

Common Mistakes

Writing the criterion without quotes. =COUNTIF(B2:B11,<>) is invalid. The criterion must be a text string in double quotes: "<>".
Using " " with a space inside. That tests for a single space, not for blank. Use "<>" with nothing between the angle brackets.
Assuming "<>" skips formulas that return "". COUNTA and COUNTIF with "<>" both count those cells, even though COUNTBLANK counts them as blank [1][2].
Counting an entire column. =COUNTIF(B:B,"<>") includes the header and any stray content far down the sheet. Use a bounded range.
Confusing blank with zero. A cell containing 0 is not blank and is counted by both COUNTIF and COUNTA [2].
Reaching for COUNTIF when COUNTA is simpler. For a plain non-blank count with no other criteria, COUNTA is shorter and easier to read [7].

Limitations

COUNTIF with "<>" cannot tell you why a cell is empty. It counts a formula returning "" as non-blank, as COUNTA does, while COUNTBLANK counts the same cell as empty [1][2]. That inconsistency means the same range can produce different counts depending on which function you pick, so document your choice when the workbook is shared.

COUNTIF also cannot distinguish a cell that looks blank from one that contains invisible characters. A space, a non-breaking space, or a stray apostrophe all register as data [6]. If your counts look wrong, inspect the cells directly instead of trusting the display. For ranges where you need several conditions at once, COUNTIF alone is not enough and you need COUNTIFS [3].

Frequently Asked Questions

What is the COUNTIF formula for not blank?

Use =COUNTIF(range,"<>"). The criterion "<>" means "not equal to nothing," so every cell holding a value is counted and every truly empty cell is skipped. Replace range with your actual cell range, such as B2:B11.

Is COUNTIF or COUNTA better for counting non-blank cells?

For a plain non-blank count with no other conditions, COUNTA is simpler and does the same job [7]. Choose COUNTIF or COUNTIFS when you want to combine the not-blank test with other criteria.

Why does COUNTIF not blank return 0?

The usual cause is a range that points at the wrong cells, such as an empty column next to your data. Invisible content such as spaces has the opposite effect, because it makes empty-looking cells count [6]. Verify the range address and inspect a few cells for stray characters.

Does COUNTIF not blank count cells with formulas?

It depends on what the formula returns. A formula that returns a number, text, or a date is counted. A formula that returns "" is also counted, by both COUNTIF with "<>" and COUNTA [1]. To skip those cells, use =SUMPRODUCT(--(LEN(range)>0)).

Can I use COUNTIFS for not blank across multiple columns?

Yes. Pass each range with its own criterion, for example =COUNTIFS(B2:B11,"<>",C2:C11,"<>"). COUNTIFS supports up to 127 range and criteria pairs, so you can stack as many not-blank tests as your data needs [3].