Excel COUNT Function
last modified October 5, 2026
The COUNT function is a fundamental statistical function in Excel.
It counts the number of cells in a range that contain numbers. This tutorial
provides a comprehensive guide to using the COUNT function. You'll
learn its syntax, practical applications, and advanced techniques to master
this essential Excel function.
COUNT Function Basics
The COUNT function counts how many cells in a range, or how many
items in a list of arguments, contain numbers. Dates and times are stored as
numbers, so they are counted too. What counts depends on how a value reaches
COUNT: inside a referenced range or array only numbers count, while
logical values, text, and error values are ignored; typed directly into the
argument list, logical values and text that looks like a number are counted as
well. The syntax is simple and flexible.
| Component | Description |
|---|---|
| Function Name | COUNT |
| Syntax | =COUNT(value1, [value2], ...) |
| Arguments | value1 required, up to 255 additional items |
| Return Value | Count of numeric values |
This table breaks down the essential components of the COUNT
function. It shows the function name, basic syntax format, argument limits, and
return value characteristics.
| Argument | Meaning |
|---|---|
| value1 | Required. The first number, cell reference, or range to count. |
| value2, ... | Optional. Additional numbers, cell references, or ranges, up to 255 additional items. |
You can mix literals, cell references, and ranges in the same call. The rules are not identical for all three, so the following examples explore each case in turn.
Basic COUNT Example
This example demonstrates the simplest use of the COUNT function with individual values.
=COUNT(5, "10", "Text", TRUE)
This formula counts 5, "10" (a numeric string typed directly), and TRUE (a
logical value typed directly), so the result is 3. Only "Text" is ignored
because it cannot be translated into a number. This shows how the position of a
value, inside a range versus directly in the argument list, changes what
COUNT includes.
COUNT with Cell References
A more practical use of COUNT involves counting numeric values
from specific cells. Here's an example with cell references.
| A | B |
|---|---|
| 10 | |
| Text | |
| 30 | |
| =COUNT(A1:A3) |
The table shows a simple spreadsheet with mixed content in column A and a
COUNT formula in cell B4 that counts numeric values from A1 to A3.
=COUNT(A1:A3)
This formula counts numeric values in cells A1 through A3. The result will be 2 (10 and 30). The text value in A2 is ignored. This demonstrates COUNT's core functionality.
Numbers Stored as Text
A number that looks numeric but is stored as text is not counted by
COUNT when it sits in a cell. This commonly happens when data is
imported or pasted, and such values are usually left aligned. The following
table shows the difference.
| A | B |
|---|---|
| 10 | |
| "20" (text) | |
| 30 | |
| =COUNT(A1:A3) |
Cell A2 holds the text "20", not the number 20, so COUNT returns 2
and counts only A1 and A3. The same text typed directly into the formula,
=COUNT("20"), is converted and counted. This is why the position of
a value matters.
=COUNT(A1:A3) ' 2, the text "20" is ignored
=COUNT("20") ' 1, a direct numeric string is counted
To convert text that looks like a number into a real number, use
VALUE, NUMBERVALUE, or the Text to Columns wizard. You
can detect the problem first with ISNUMBER.
COUNT with Dates
COUNT treats dates as numbers since Excel stores dates as serial numbers. This example shows COUNT with date values.
| A | B |
|---|---|
| 1/1/2023 | |
| Meeting | |
| 2/1/2023 | |
| =COUNT(A1:A3) |
This table demonstrates how COUNT handles dates mixed with text.
The dates in A1 and A3 are counted as numbers, while the text in A2 is ignored.
=COUNT(A1:A3)
This formula counts the dates in A1 and A3 as numeric values. The result is 2. This shows that COUNT works with dates since Excel stores them as numbers.
COUNT with Logical Values
Inside a referenced range, COUNT ignores logical values (TRUE/FALSE). This example demonstrates this behavior.
| A | B |
|---|---|
| 10 | |
| TRUE | |
| FALSE | |
| =COUNT(A1:A3) |
The table shows how COUNT handles logical values mixed with
numbers. Only the numeric value in A1 is counted, while TRUE and FALSE are
ignored.
=COUNT(A1:A3)
This formula counts only A1 (10), ignoring A2 (TRUE) and A3 (FALSE). The result is 1. To count logical values, you would need to use COUNTA instead.
COUNT with Error Values
COUNT ignores cells containing error values in its calculation. This example shows this behavior.
| A | B |
|---|---|
| 10 | |
| #N/A | |
| 20 | |
| =COUNT(A1:A3) |
The table demonstrates how COUNT reacts when encountering error
values in the range. The #N/A error in A2 is ignored in the count.
=COUNT(A1:A3)
This formula counts A1 (10) and A3 (20), ignoring A2 (#N/A error). The result is 2. Unlike SUM, COUNT doesn't propagate errors in referenced cells.
COUNT with Multiple Ranges
COUNT can handle multiple ranges or combinations of ranges and individual cells. This example shows this flexibility.
| A | B | C |
|---|---|---|
| 5 | Text | |
| 15 | 20 | |
| =COUNT(A1:B2, 25) |
This table demonstrates COUNT's ability to combine different types of arguments. It shows values in cells A1 through B2 plus an additional number 25.
=COUNT(A1:B2, 25)
This formula counts numeric values in range A1:B2 (5, 15, 20) plus the number 25. The text in B1 is ignored. The result will be 4. This demonstrates COUNT's flexibility with arguments.
COUNT vs. COUNTA
It's important to distinguish between COUNT and COUNTA. COUNT only counts numeric values, while COUNTA counts all non-empty cells regardless of content.
| A | B | C |
|---|---|---|
| 10 | ||
| Text | ||
| TRUE | ||
| =COUNT(A1:A3) | =COUNTA(A1:A3) |
The table compares COUNT and COUNTA results for the same range. COUNT returns 1 (only the number 10), while COUNTA returns 3 (all non-empty cells).
=COUNT(A1:A3) // Returns 1 =COUNTA(A1:A3) // Returns 3
These formulas demonstrate the key difference between COUNT and COUNTA. COUNT is specific to numbers, while COUNTA counts any content. Choose the appropriate function based on your needs.
COUNT vs. COUNTBLANK vs. COUNTIF
Excel offers several related counting functions, and each one answers a different question. The table below compares them for the same range, A1:A3, which holds the number 10, the text "Text", and the logical value TRUE.
| Function | What it counts | Result for A1:A3 |
|---|---|---|
| COUNT | Cells that contain numbers | 1 |
| COUNTA | All non-empty cells, including text and logical values | 3 |
| COUNTBLANK | Empty cells, and formulas that return "" | 0 |
| COUNTIF | Cells that meet a single criterion | 1 for "=10" |
COUNT returns 1 because only 10 is a number.
COUNTA returns 3 because every cell holds something.
COUNTBLANK returns 0 because no cell is empty.
COUNTIF returns 1 when asked to count cells equal to 10. Use
COUNTIFS when you need more than one criterion, and
COUNTBLANK when you specifically need to find gaps in your data.
=COUNT(A1:A3) ' 1, numbers only =COUNTA(A1:A3) ' 3, all non-empty cells =COUNTBLANK(A1:A3) ' 0, no empty cells =COUNTIF(A1:A3,10) ' 1, cells equal to 10
COUNT with Named Ranges
COUNT works seamlessly with named ranges, making formulas more readable. This example shows COUNT with a named range.
| A | B |
|---|---|
| 100 | |
| Text | |
| 300 | |
| =COUNT(SalesData) |
The table assumes cells A1:A3 are named "SalesData". The COUNT formula references this named range instead of cell addresses, improving formula clarity.
=COUNT(SalesData)
This formula counts numeric values in the named range "SalesData" (A1:A3). The result is 2 (100 and 300). Named ranges make formulas more understandable and maintainable.
COUNT with Dynamic Arrays
In modern Excel versions, COUNT can work with dynamic array formulas. This example demonstrates counting numeric values in a filtered range.
=COUNT(FILTER(A1:A10, B1:B10="Active"))
This formula counts numeric values in A1:A10 where corresponding cells in B1:B10 contain "Active". The FILTER function creates a dynamic array that COUNT then processes. This enables conditional counting without COUNTIF.
COUNT Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Numbers stored as text in cells | Count is lower than expected | Convert the text with VALUE, NUMBERVALUE, or Text to Columns |
| Logical values in a range | TRUE and FALSE are skipped | COUNT ignores logicals in references; use COUNTA if they must count |
| Error values in a referenced range | Errors are silently ignored | COUNT does not flag them; test the range with ISERROR or COUNTIF |
| Counting text or blanks | COUNT returns 0 or too small a number | Use COUNTA for non-empty cells and COUNTBLANK for empty cells |
| Counting with a condition | COUNT has no criteria argument | Use COUNTIF or COUNTIFS instead |
| Hidden or filtered rows | Hidden values are still counted | COUNT includes hidden rows; use SUBTOTAL or AGGREGATE |
The COUNT function is essential for data analysis in Excel. From
basic counting to dynamic array operations, it provides reliable numeric cell
counting. The key subtlety is where a value comes from: inside a range or array,
COUNT counts numbers only and quietly ignores text, blanks, logical
values, and errors, while a value typed directly into the argument list is
counted if Excel can read it as a number, including numeric text and logical
values. When you need to count non-empty cells, blanks, or cells that meet a
condition, reach for COUNTA, COUNTBLANK, or
COUNTIF. Mastering these distinctions will make your counts
accurate and easy to explain.
Author
List all Excel Formulas.