To count cells in Excel, use the function that matches what you’re counting: =COUNTA(A2:A100) counts every cell that isn’t empty, =COUNT(A2:A100) counts only cells with numbers, =COUNTBLANK(A2:A100) counts empty cells, and =COUNTIF(A2:A100,"Yes") counts cells that match a condition. For a quick answer without a formula, select the cells and read Count in the status bar at the bottom of the window.
Quick count: the status bar
Select a range, and Excel shows Count (non-empty cells) at the bottom-right. Right-click the status bar to also show Numerical Count, which counts only numbers. This is handy for checking, but it isn’t saved on the sheet.
The counting functions
| Function | Counts | Example |
|---|---|---|
COUNT | Cells containing numbers (including dates) | =COUNT(B2:B100) |
COUNTA | Cells that aren’t empty: text, numbers, errors, formulas | =COUNTA(A2:A100) |
COUNTBLANK | Empty cells, and formulas returning “” | =COUNTBLANK(C2:C100) |
COUNTIF | Cells meeting one condition | =COUNTIF(D2:D100,">500") |
COUNTIFS | Cells meeting several conditions | =COUNTIFS(D2:D100,">500",E2:E100,"East") |
ROWS / COLUMNS | Cells in a range, filled or not | =ROWS(A2:A100)*COLUMNS(A2:D2) |
Step by step: write a counting formula
- Click the empty cell where you want the result, for example below your data or on a summary sheet.
- Type
=COUNTIF(and drag across the cells you want to check. Excel fills in the range. - Type a comma, then the condition in double quotes, such as
"Paid"or">500". - Type
)and press Enter. - To reuse the formula for other values, put the condition in a cell (say F1) and refer to it instead:
=COUNTIF(A2:A100,F1). Then you only change F1.
COUNT vs COUNTA: which one?
The difference trips people up. Say column A has 20 names and column B has scores, with three people who haven’t taken the test yet. =COUNTA(A2:A21) returns 20, because every name cell has something in it. =COUNT(B2:B21) returns 17, because three score cells are blank. If one of those scores were typed as “absent”, COUNT would still return 17, while COUNTA on column B would return 18. Use COUNTA when you care whether a cell has anything, and COUNT when you only want numbers.
Worked example: an attendance sheet
Imagine names in A2:A30 and each day’s attendance in columns B to F, marked P for present and A for absent. In G2, =COUNTIF(B2:F2,"P") counts the days each person attended. Copy it down the column. Under each day, =COUNTIF(B2:B30,"A") counts absences, and =COUNTBLANK(B2:B30) flags anyone not yet marked. For a full template, see how to make an attendance sheet in Excel.
COUNTIF examples
- Exact text:
=COUNTIF(A2:A100,"Paid")(not case-sensitive). - Text containing a word:
=COUNTIF(A2:A100,"*late*"). - Numbers above a value:
=COUNTIF(B2:B100,">=1000"). - Compared with another cell:
=COUNTIF(B2:B100,">"&F1). - Not blank:
=COUNTIF(A2:A100,"<>"). - Dates after a date:
=COUNTIF(C2:C100,">"&DATE(2026,1,1)).
For counting how often each value appears, see how to count the number of occurrences in Excel.
Count unique values
In Excel for Microsoft 365 and 2021 or later, =COUNTA(UNIQUE(A2:A100)) counts distinct entries. If the range has blanks, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))).
Count visible cells only
After filtering, =SUBTOTAL(103,A2:A100) counts non-empty visible cells and =SUBTOTAL(102,A2:A100) counts visible numbers. See how to count the number of rows in Excel.
Count cells by color
No standard function counts by fill color. Use Find, a filter or a small VBA function, as shown in how to count highlighted cells in Excel.
Troubleshooting
COUNT returns 0 but there are numbers
The numbers are stored as text. Use COUNTA, or convert them with the warning icon’s Convert to Number.
COUNTA counts cells that look empty
Those cells contain a space, an apostrophe or a formula returning “”. Use =SUMPRODUCT(--(LEN(TRIM(A2:A100))>0)) to count only cells with visible content.
COUNTIF with text doesn’t match
Check for extra spaces in the data. Clean it with TRIM, or use a wildcard such as "*Paid*".
Frequently asked questions
Can COUNTIF be case-sensitive?
No. Use =SUMPRODUCT(--EXACT(A2:A100,"Paid")) for a case-sensitive count.
How do I add numbers instead of counting them?
Use SUM or SUMIF. See how to add numbers in Excel.
Summary
- Non-empty cells:
COUNTA. - Numbers only:
COUNT. - Empty cells:
COUNTBLANK. - With a condition:
COUNTIForCOUNTIFS. - Quick check: select cells and read Count in the status bar.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.