=COUNTIF(B2:B8,"<>") counts the cells in B2:B8 that are not empty. <> means "not equal to", and with nothing after it the comparison is with an empty cell. =COUNTA(B2:B8) gives the same number.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Phone | Count | Result | ||
| 2 | Ana | ana@mail.com | 555-0101 | Emails, COUNTIF | 5 | |
| 3 | Ben | 555-0102 | Emails, COUNTA | 5 | ||
| 4 | Cara | cara@mail.com | Missing emails | 2 | ||
| 5 | Dan | dan@mail.com | 555-0104 | Phones | 4 | |
| 6 | Eva | Rows | 7 | |||
| 7 | Finn | finn@mail.com | 555-0106 | |||
| 8 | Gus | gus@mail.com |
Five of the seven contacts have an email, so F2 and F3 both show 5, and COUNTBLANK counts the two missing ones. The filled and empty counts always add up to the number of rows in F6. Type an email into B3 and F2 goes up by one while F4 goes down.
COUNTIF not blank vs COUNTA
For one range, =COUNTIF(B2:B8,"<>") and =COUNTA(B2:B8) count the same cells: anything that is not empty, including numbers, dates, TRUE/FALSE and error values. COUNTA is shorter. The "<>" form exists for the moment you need a second condition, because COUNTA cannot take one and COUNTIFS can.
COUNT is a different function: it counts only numbers and dates, so a column of names gives 0. The three are compared on COUNT and COUNTA.
The opposite of not blank is "": =COUNTIF(B2:B8,"") counts the empty cells, like COUNTBLANK.
COUNTIFS not blank with another condition
Use "<>" as one of the COUNTIFS criteria. Below, a ship date means the order has gone out.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Order | Shipped | Condition | Count | |
| 2 | North | A-101 | 2026-01-08 | North, shipped | 2 | |
| 3 | South | A-102 | North, not shipped | 1 | ||
| 4 | North | A-103 | 2026-02-06 | Any region, shipped | 3 | |
| 5 | East | A-104 | 2026-02-20 | Order and date filled | 3 | |
| 6 | South | A-105 | ||||
| 7 | North | A-106 |
F2 counts the two North orders with a date, F3 the one without. F5 puts "<>" on two columns, so it counts the rows where both are filled. The same criterion works in SUMIFS and AVERAGEIFS: =SUMIFS(D2:D7,C2:C7,"<>") would add an amount column D for the shipped rows only.
Cells that only look empty
A cell with a space in it looks empty but holds text, and "<>" and COUNTA count it. That happens with data pasted from other systems or "cleared" by typing a space. Count the cells with visible content by trimming the spaces and checking the length.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Note | Count | Result | |
| 2 | Ana | call back | COUNTA | 4 | |
| 3 | Ben | Visible content | 2 | ||
| 4 | Cara | Space in B3? | 1 | ||
| 5 | Dan | paid | |||
| 6 | Eva |
B3 holds one space and B6 two. COUNTA counts them with the two real notes and shows 4. The SUMPRODUCT version trims each cell and counts only those with something left: 2. To clean the cells themselves, select them and press Delete, or use TRIM in a helper column. Replacing every space with nothing in Find and Replace would also turn "call back" into "callback".
Formulas that return an empty string
A formula like =IF(C2>0,C2,"") shows nothing when the condition is false, but the cell is not empty: it holds a formula whose result is an empty string. Different counting functions treat it differently. For A2:A6 holding Apple, a formula returning "", an empty cell, 5 and Pear, Excel gives:
=COUNTA(A2:A6) 4 counts the "" formula
=COUNTIF(A2:A6,"<>") 4 counts the "" formula
=COUNTBLANK(A2:A6) 2 counts the "" formula as blank
=COUNTIF(A2:A6,"") 2 counts the "" formula as blank
=SUMPRODUCT(--(LEN(A2:A6)>0)) 3 counts only visible content
=COUNTIF(A2:A6,"?*") 2 counts text of at least one character
So the formula's cell is counted both as blank and as not blank, and COUNTA plus COUNTBLANK is more than the number of cells. When a column is filled with such formulas, count with =SUMPRODUCT(--(LEN(A2:A6)>0)), which counts only cells that show something. With a second condition: =SUMPRODUCT((B2:B6="North")*(LEN(A2:A6)>0)).
Practice: not blank plus a condition
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | City | Phone | Count | Result | |
| 2 | Ana | Paris | 555-0101 | Paris with phone | ||
| 3 | Ben | Lyon | 555-0102 | |||
| 4 | Cara | Paris | ||||
| 5 | Dan | Paris | 555-0104 | |||
| 6 | Eva | Lyon | ||||
| 7 | Finn | Nice | 555-0106 | |||
| 8 | Gus | Paris |
Your turn: Count the contacts in Paris that have a phone number in column C. Write the formula in F2.
Which formula to use
| You want to count | Formula |
|---|---|
| Filled cells in one range | =COUNTA(B2:B8) |
| Filled cells plus another condition | =COUNTIFS(A2:A8,"North",B2:B8,"<>") |
| Rows where two columns are filled | =COUNTIFS(B2:B8,"<>",C2:C8,"<>") |
Cells with visible content, skipping spaces and "" formulas | =SUMPRODUCT(--(LEN(TRIM(B2:B8))>0)) |
| Filled text cells only | =COUNTIF(B2:B8,"?*") |
| Empty cells | =COUNTBLANK(B2:B8) |
To test a single cell instead of counting, =B2<>"" returns TRUE when B2 shows something, and =ISBLANK(B2) returns TRUE only when B2 is truly empty, with no formula in it.
Frequently Asked Questions
Is COUNTIF "<>" the same as COUNTA?
For one range, yes: =COUNTIF(B2:B8,"<>") and =COUNTA(B2:B8) count the same cells (text, numbers, dates, TRUE/FALSE and errors). The "<>" form is for COUNTIFS, where it can sit next to another condition.
What does "<>" mean in COUNTIF?
<> is "not equal to", and with nothing after it the comparison is with an empty cell. So "<>" means "not empty", and "<>North" means "not equal to North".
How do I count cells that are not blank and not zero?
Use =COUNTIFS(B2:B8,"<>",B2:B8,"<>0"). The criterion "<>0" on its own also counts empty cells, because an empty cell is not equal to 0; adding "<>" leaves them out.
Why does COUNTA count cells that look empty?
They are not empty: they hold a space, or a formula that returns an empty string "". To count only cells with visible content, use =SUMPRODUCT(--(LEN(TRIM(B2:B8))>0)).
