Count Cells with Numbers in Excel: 4 Easy Ways
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to count cells that contain numbers in Microsoft Excel, covering the COUNT, COUNTA, COUNTIF, and SUMPRODUCT functions, and how to handle mixed data. You will learn the exact formula for each method, when to use it, and how to avoid common errors like counting text or blank cells.
Formula
The core function for counting numeric cells is COUNT. Its syntax is:
=COUNT(value1, [value2], ...)
where value1 is the first cell, range, or array you want to count, and value2 and beyond are optional additional ranges (up to 255 in modern Excel). The function counts only cells that contain numbers, including dates and times (which are stored as numbers in Excel). It ignores empty cells, text, logical values (TRUE/FALSE), and errors.
In the calculator above, count(d) applies the same logic to the range you enter, giving the total count of numeric cells.
Worked Example
Suppose you have the following data in cells A1:A10:
- A1: 42
- A2: “apple”
- A3: 3.14
- A4: (blank)
- A5: 100
- A6: TRUE
- A7: 0
- A8: “123” (text)
- A9: 7/1/2024 (date)
- A10: #DIV/0! (error)
Using the formula =COUNT(A1:A10), Excel counts:
- 42 – yes
- “apple” – no
- 3.14 – yes
- blank – no
- 100 – yes
- TRUE – no (logical)
- 0 – yes
- “123” – no (text)
- 7/1/2024 – yes (date is a number)
- error – no
So the result is 5. The calculator above with the default range A1:A10 and your actual data will give the same result.
When to Use COUNT vs COUNTA vs COUNTIF
Excel offers several counting functions, each designed for a different purpose. Choosing the right one depends on what you want to count:
- COUNT: Counts cells that contain numbers (including dates and times). Use this when you only want numeric values.
- COUNTA: Counts all non-empty cells, regardless of content (numbers, text, logicals, errors). Use this to count entries that are not blank.
- COUNTBLANK: Counts empty cells. Useful for identifying gaps in data.
- COUNTIF: Counts cells that meet a specific condition, such as numbers greater than 10 or text that equals “Yes”. For numbers, you can use criteria like “>10” or “<>0″.
- COUNTIFS: Applies multiple criteria across multiple ranges. For example, count numbers in column A that are between 1 and 100.
For a simple count of numeric cells, COUNT is the most direct. But if you need to count numbers that meet a condition (e.g., only positive numbers), use COUNTIF or COUNTIFS.
How to Count Numbers with Conditions
Sometimes you need to count only numbers that satisfy a condition. The COUNTIF function is perfect for this. Its syntax is:
=COUNTIF(range, criteria)
Examples:
- Count numbers greater than 50:
=COUNTIF(A1:A10, ">50") - Count numbers not equal to 0:
=COUNTIF(A1:A10, "<>0") - Count numbers between 10 and 20:
=COUNTIFS(A1:A10, ">=10", A1:A10, "<=20")
Note that criteria are always in quotes. For numeric comparisons, Excel treats the criteria as a number even though it is text.
For more complex conditions, you can use SUMPRODUCT. For example, to count numbers that are integers (whole numbers) in a range:
=SUMPRODUCT(--(MOD(A1:A10,1)=0))
This works by testing each cell for a remainder when divided by 1; if the remainder is 0, the number is an integer. The double unary (--) converts TRUE/FALSE to 1/0, and SUMPRODUCT sums them.
Common Mistakes and How to Avoid Them
Counting cells with numbers seems simple, but several pitfalls can lead to wrong results:
- Counting text that looks like numbers: If a cell contains "123" as text (often indicated by a green triangle or left-aligned), COUNT ignores it. To convert text to numbers, use the VALUE function or Text to Columns.
- Forgetting that dates and times are numbers: COUNT includes dates and times. If you want to exclude them, you need a more complex formula, such as
=SUMPRODUCT(--(ISNUMBER(A1:A10))*(A1:A10<1))to count only times (which are fractions of a day). - Including entire columns: Using
=COUNT(A:A)counts all numbers in column A, but it also counts numbers in header rows or hidden rows. Be specific with your range. - Mixing data types in a range: If your range includes errors, COUNT ignores them, but other functions like SUM may not. Always check for errors in your data before relying on counts.
- Using COUNTA when you need COUNT: COUNTA counts non-empty cells, so it will include text and logical values. If you only want numbers, use COUNT.
Alternatives for Special Cases
If your data is not a simple range, or you need to count numbers in a more dynamic way, consider these alternatives:
- SUBTOTAL: Use
=SUBTOTAL(2, range)to count numbers in filtered or hidden rows. The function number 2 (or 102 for ignoring hidden rows) counts numeric cells. - AGGREGATE: Similar to SUBTOTAL but with more options. Use
=AGGREGATE(2, 5, range)to count numbers while ignoring hidden rows and error values. - Array formulas: In older Excel versions, you might need to enter formulas with Ctrl+Shift+Enter. For example,
=SUM(IF(ISNUMBER(A1:A10),1,0))as an array formula. - Power Query: If you are working with large datasets, you can load data into Power Query and use the "Count" transformation to count numeric values.
Each method has its place, but for most everyday tasks, COUNT is sufficient.
Frequently Asked Questions
How do I count cells with numbers in a specific range?
Use the formula =COUNT(A1:A10) where A1:A10 is your range. This will return the number of cells that contain numbers, including dates and times.
Why does COUNT return 0 when I have numbers in my range?
This usually happens when the numbers are stored as text. Check if the cells are left-aligned (text) instead of right-aligned (numbers). Select the cells, click the warning icon, and choose "Convert to Number" or use the VALUE function in a helper column.
Does COUNT count blank cells?
No, COUNT ignores blank cells. If you want to count non-blank cells (including text), use COUNTA. If you want to count blank cells, use COUNTBLANK.
How do I count numbers greater than a certain value?
Use COUNTIF with a condition, like =COUNTIF(A1:A10, ">50"). For multiple conditions, use COUNTIFS.
Can I count numbers in non-contiguous cells?
Yes, you can list individual cells in the COUNT function, like =COUNT(A1, B3, C5). You can also use multiple ranges, like =COUNT(A1:A5, C1:C5).
How do I count numbers in a row instead of a column?
Simply change the range to a row, like =COUNT(1:1) to count all numbers in row 1, or =COUNT(A1:F1) for a specific range.
Related
Formula used
in d | Data range | - | A1:A10 | 0 out count | Count of numeric cells | - | count(d) | 0
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.