Excel guide · 4 min read
How to count cells that are not blank in Excel
=COUNTA(B2:B9) to count the cells in B2:B9 that are not empty. =COUNTIF(B2:B9,"<>") does the same for typed values, and COUNTIFS lets you add a not-blank test to other conditions, as in =COUNTIFS(C2:C9,"Yes",B2:B9,"<>"). Both count cells that hold only a space, and COUNTA also counts formulas that return "".Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Phone | Paid | Follow-up |
| 2 | Ana Lopes | 555-0101 | Yes | |
| 3 | Ben Carter | 555-0187 | No | Send reminder |
| 4 | Chloe Dubois | Yes | ||
| 5 | Dev Patel | 555-0142 | Yes | |
| 6 | Elena Petrova | No | Send reminder | |
| 7 | Femi Adeyemi | 555-0119 | Yes | |
| 8 | Grace Kim | No | Get phone | |
| 9 | Hugo Martin | 555-0163 | Yes |
Download this sheet as an Excel practice file (.xlsx)
COUNTA: the quick answer
The sheet lists course sign-ups with a phone number, a paid flag and a follow-up note. To count how many people gave a phone number, COUNTA counts every cell in the range that is not empty: text, numbers, dates, TRUE/FALSE and errors all count.
There are 8 people and Chloe and Grace left the phone empty, so COUNTA says 6. But only five people gave a number: Elena's cell holds a single space. More on that below.
Result: 6
6: five phone numbers plus the cell with a space.
COUNTIF with "<>" and COUNTIFS with a not-blank condition
"<>" on its own means "not equal to nothing", so COUNTIF(B2:B9,"<>") counts the filled cells. On typed data it gives the same 6 as COUNTA. Its real use is inside COUNTIFS, where it becomes one condition among several. A row counts only when every condition is true.
Here: paid sign-ups who gave a phone number. Swap "<>" for "" to count the paid ones with no number. Every range in COUNTIFS must be the same size and start on the same row.
Result: 4
4: Ana, Dev, Femi and Hugo. Chloe paid but left the phone empty.
Cells that hold only a space
A space is a character, so COUNTA and "<>" both count it, and the cell looks empty on screen. Spaces come from people "clearing" a cell with the space bar and from pasted or exported data. To count only cells with real content, trim each cell and compare it with "": =SUMPRODUCT(--(TRIM(B2:B9)<>"")). TRIM removes the spaces, the comparison gives TRUE or FALSE per row, and -- turns them into 1s and 0s for SUMPRODUCT to add.
If you would rather fix the data, select the column and use Home > Find & Select > Replace to replace a space with nothing (on a column of names this also removes the space between first and last name, so only do it on columns like phone numbers or IDs).
Result: 5
5: the real phone numbers. Elena's space no longer counts.
Formulas that return "" are counted by COUNTA
Column D holds a formula in every row: it returns "" (nothing) for paid sign-ups and a reminder for the rest. On screen only three cells show text, but every cell contains a formula, so COUNTA counts all 8. COUNTA counts cells that are not empty, and a formula cell is never empty, whatever it shows.
Compare each cell with "" instead. A formula that returns "" is equal to "", so =SUMPRODUCT(--(D2:D9<>"")) counts only the cells that show something. For text results, =COUNTIF(D2:D9,"?*") also works: ?* means at least one character, so the empty results are skipped. It ignores numbers, though, so use the SUMPRODUCT version on mixed columns. Do not rely on COUNTIF with "<>" to skip these cells; the comparison test is the safe choice.
Result: 3
3: Ben, Elena and Grace need a follow-up. =COUNTA(D2:D9) returns 8 for the same range.
Not blank plus another condition, with the space fix
COUNTIFS cannot trim, so when the column may hold spaces, put both tests inside SUMPRODUCT and multiply them. Each test returns TRUE or FALSE per row, and multiplying keeps only the rows where both are TRUE.
Unpaid sign-ups with a usable phone number come out at 1: only Ben. Elena is unpaid too, but her phone cell is a space, so she is left out.
Result: 1
1: Ben is the only unpaid sign-up with a real phone number.
Which one to pick
Clean, typed data: COUNTA, or COUNTIFS with "<>" when there are other conditions. Cells that might hold spaces, or formula columns that return "": SUMPRODUCT with a comparison to "" (and TRIM for spaces). Text only: COUNTIF with "?*". For a filtered list, =SUBTOTAL(3,B2:B9) counts non-empty cells in the visible rows only; the app's engine cannot run SUBTOTAL, so it has no live result here.
A quick check: count the blanks with COUNTBLANK and add the result to your not-blank count. If the two numbers add up to more than the rows in the range, some cells (usually "" formulas) are being counted by both.
Common mistakes
Expecting COUNTA to skip cells that look empty
COUNTA counts spaces and formulas that return "". Use =SUMPRODUCT(--(TRIM(B2:B9)<>"")) to count what is actually visible.
Writing "<>""" for not blank
The not-blank criteria is just "<>". The extra quotes make Excel look for cells that are not equal to a quote character, which counts the empty cells too.
Using COUNT for text columns
COUNT only counts numbers. Phone numbers typed with dashes are text, so COUNT skips them. Use COUNTA.
Including the header in the range
COUNTA(B:B) counts the header too. Start at row 2 or subtract 1.
FAQ
What formula counts cells that are not blank in Excel?
=COUNTA(B2:B9). It counts any cell that is not empty, including numbers, text, errors and formulas. With other conditions, use COUNTIFS(...,B2:B9,"<>").
Why does COUNTA count blank cells?
The cells are not truly empty. They hold a space or a formula that returns "". Use =SUMPRODUCT(--(TRIM(B2:B9)<>"")) to count only cells that show something.
What is the difference between COUNTA and COUNTIF "<>"?
On typed data they give the same result. COUNTIF and COUNTIFS let you combine the not-blank test with other conditions; COUNTA has no conditions.
How do I count non-blank cells that meet a condition?
=COUNTIFS(C2:C9,"Yes",B2:B9,"<>") counts rows where C is Yes and B is filled. If B might hold spaces, use =SUMPRODUCT((C2:C9="Yes")*(TRIM(B2:B9)<>"")).
Functions used
COUNTACounts non-empty cellsCOUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsSUMPRODUCTMultiply then addTRIMRemoves extra spacesCOUNTBLANKCounts empty cellsCOUNTCounts numbers
Related guides
- How to count blank cells in Excel
- COUNT vs COUNTA vs COUNTBLANK
- How to count cells with text in Excel
- How to check if a cell is blank in Excel
- SUMIF not blank: sum if the cell next to it is not empty
- How to count Yes and No answers in Excel
- How to count duplicates in Excel
Updated