Count Cells in Range: Excel COUNTA & COUNT
- Range size (cells)
- 10 cells
- You entered
- Filled cells
- 7 cells
- You entered
To count cells in a range in Excel, use the COUNTA function to count all non-empty cells, or the COUNT function to count only cells containing numbers. For example, =COUNTA(A1:A10) returns the number of cells in that range that are not blank, while =COUNT(A1:A10) returns the count of numeric entries.
Formula
The calculator above works out the percentage of cells in a range that contain data. The formula is:
Fill percentage = (Filled cells / Total cells in range) × 100
- Filled cells – the number of cells with any content (numbers, text, dates, errors).
- Total cells in range – the number of cells in the selected range (rows × columns).
This is not an Excel function itself, but it helps you check how complete a dataset is before you decide which counting function to use.
Worked Example
Using the defaults from the calculator: a range of 10 cells, with 7 filled.
- Divide filled by total: 7 ÷ 10 = 0.7
- Multiply by 100: 0.7 × 100 = 70%
The result is 70.0%, which matches the calculator output.
When to Use Each Counting Function
Excel offers several ways to count cells, and choosing the right one depends on what you need to count.
- COUNT – counts cells that contain numbers only. Use it for numeric data like sales figures, ages, or scores. Text, blanks, and errors are ignored.
- COUNTA – counts all non-empty cells, including text, numbers, dates, and even errors. Use it to see how many rows have any data at all.
- COUNTBLANK – counts empty cells. Useful for spotting missing entries.
- COUNTIF – counts cells that meet one condition, like =COUNTIF(B2:B20,”>10″).
- COUNTIFS – counts cells that meet multiple conditions across different ranges.
For a simple “how many cells are filled?” question, COUNTA is the go-to. For “how many numbers are here?” use COUNT.
How to Count Cells in a Range Step by Step
- Click the cell where you want the result to appear.
- Type =COUNTA( to count non-empty cells, or =COUNT( to count numeric cells.
- Select the range with your mouse, or type it manually, e.g., A1:A20.
- Close the parenthesis and press Enter.
For example, =COUNTA(C2:C100) will tell you how many cells in that column have any content. If you need to count only cells with numbers, use =COUNT(C2:C100).
Common Mistakes and Misconceptions
A frequent mistake is using COUNT when you want to count text entries. COUNT ignores text, so you get a lower number than expected. Another common issue is counting cells that contain formulas that return empty strings (“”). These cells look blank but COUNTA counts them as non-empty because they contain a formula. To avoid this, use =COUNTIF(range,”?*”) to count cells with actual text, or =SUMPRODUCT(–(LEN(range)>0)) to count cells with any characters.
Also, remember that COUNT counts numbers stored as text? No, it does not. If a number is formatted as text, COUNT ignores it. You may need to convert text to numbers first.
Limits and Alternatives
COUNTA and COUNT work on a single range or up to 255 ranges in older versions, but they only count cells in the ranges you specify. They do not count cells based on color, font, or conditional formatting. For those, you need VBA or the SUBTOTAL function with filtered data.
If you need to count cells that match a specific value, use COUNTIF. For example, =COUNTIF(A1:A10,”Yes”) counts how many cells say “Yes”. For multiple criteria, COUNTIFS is the right tool.
When working with entire rows or columns, be careful: =COUNTA(A:A) counts every non-empty cell in column A, which may include header cells or stray data. Limit the range to your actual data area.
Frequently Asked Questions
What is the difference between COUNT and COUNTA?
COUNT only counts cells containing numbers. COUNTA counts all non-empty cells, including text, dates, and errors. If you have a list of names, COUNTA will count them; COUNT will return 0.
How do I count cells with text in Excel?
Use =COUNTIF(range,”*”) to count cells that contain any text. This counts cells with text, but ignores numbers and blanks. For cells with specific text, replace * with the text, e.g., =COUNTIF(A1:A10,”apple”).
Why does COUNTA count a cell that looks empty?
If a cell contains a formula that returns an empty string (“”), or has a space character, it is not truly blank. COUNTA counts it as non-empty. Use =COUNTBLANK(range) to count truly blank cells, or check for spaces with =LEN(range)=0.
How do I count cells with numbers greater than a value?
Use COUNTIF with a condition: =COUNTIF(A1:A10,”>10″) counts cells greater than 10. For greater than or equal to, use “>=10”.
Can I count cells based on cell color?
No standard Excel function counts by color. You would need to use VBA or filter by color and then use SUBTOTAL. The SUBTOTAL function with function number 2 or 3 counts visible cells only.
How do I count non-empty cells in multiple ranges?
COUNTA can take multiple arguments: =COUNTA(A1:A10, C1:C10) counts non-empty cells in both ranges. The total is the sum of both counts.
Related
Formula used
in r | Range size (cells) | cells | 10 | 1 in f | Filled cells | cells | 7 | 0 out pct | Fill percentage | % | f / r * 100 | 1
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.