# COUNT Function in Excel: Syntax, Examples and Tips

The COUNT function in Excel counts the number of cells that contain numbers. It ignores blank cells, text and error values, so it is the right tool when you need to know how many numeric entries a range holds. This article covers the syntax, a worked example, error fixes and the mistakes that cause wrong counts.

## Quick Answer

- COUNT returns the number of cells that contain numbers in the ranges or values you give it [1].
- Its syntax is `=COUNT(value1, [value2], ...)`, where `value1` is required and up to 255 more items are optional [1].
- Numbers, dates and text that looks like a number (such as `"1"`) are counted when typed directly into the arguments [1].
- Empty cells, logical values, text and error values inside a range or array are not counted [1].
- To count text or logical values, use COUNTA. To count numbers that meet criteria, use COUNTIF or COUNTIFS [1].

## Syntax

The COUNT function takes one required argument and up to 255 optional ones [1].

| Argument | Required? | Meaning |
|---|---|---|
| `value1` | Required | The first item, cell reference or range in which you want to count numbers [1]. |
| `value2, ...` | Optional | Up to 255 additional items, cell references or ranges to count [1]. |

The general form is:

$$=\text{COUNT}(value1,\ value2,\ \dots)$$

Each argument can be a single cell, a range, an array or a literal value. COUNT adds up the numeric entries across all of them and returns a single number.

## How It Works

COUNT walks through every cell in the arguments you supply and checks whether the content is a number. If it is, the count goes up by one. If it is not, the cell is skipped.

The rule that matters most is what counts as a number. According to Microsoft, arguments that are numbers, dates or a text representation of numbers are counted [1]. That means a date like `1/15/2024` is stored as a serial number and is counted. A value typed as `"1"` with quotation marks is also counted because it is a text representation of a number [1].

What does not count is just as important. Arguments that are error values or text that cannot be translated into numbers are not counted [1]. When an argument is an array or reference, only numbers in that array or reference are counted, and empty cells, logical values, text and error values are not counted [1].

This behavior is why COUNT is useful for data cleaning. If you expect 100 numeric measurements and COUNT returns 92, you know eight cells are blank, text or something else. You can then inspect those cells before running any calculation on the column.

If you need to count logical values, text or error values, use the COUNTA function instead [1]. If you want to count only numbers that meet certain criteria, use COUNTIF or COUNTIFS [1].

## Worked Example

The dataset below is a small lab measurement sheet with a Sample ID, a Measurement column and a Notes column. Column B holds the measurements, and some rows are blank or contain text.

| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Sample ID | Measurement | Notes | Numeric count |
| 2 | S-01 | 12.4 | numeric | `=COUNT(B2:B13)` -> displays 8 |
| 3 | S-02 | 15.8 | numeric | |
| 4 | S-03 | | blank | |
| 5 | S-04 | 9.6 | numeric | |
| 6 | S-05 | N/A | text | |
| 7 | S-06 | 18.2 | numeric | |
| 8 | S-07 | 11.1 | numeric | |
| 9 | S-08 | | blank | |
| 10 | S-09 | 14.7 | numeric | |
| 11 | S-10 | pending | text | |
| 12 | S-11 | 16.3 | numeric | |
| 13 | S-12 | 13.9 | numeric | |

The formula in cell D2 is:

```
=COUNT(B2:B13)
```

It returns **8**. The range B2:B13 has 12 rows. Eight of them contain numbers: 12.4, 15.8, 9.6, 18.2, 11.1, 14.7, 16.3 and 13.9. Two cells are blank (B4 and B9) and two contain text (B6 holds "N/A" and B11 holds "pending"). COUNT ignores all four of those, so the result is 8.

This is the core value of COUNT. It tells you how many usable numbers you have without you having to scan the column by eye.

## More Examples

**Count a single range.** `=COUNT(A1:A20)` returns the number of numeric cells in that range. Microsoft uses this exact example, noting that if five cells contain numbers the result is 5 [1].

**Count several ranges at once.** `=COUNT(B2:B13, D2:D13)` adds the numeric cells in both ranges and returns one total.

**Count literal values typed into the formula.** `=COUNT(1, 2, "3", "text")` counts the three numeric items and ignores the text, so it returns 3. Logical values and text representations of numbers typed directly into the argument list are counted [1].

**Count dates.** `=COUNT(C2:C50)` counts date entries because dates are stored as numbers [1]. This is handy when you want to confirm every row has a valid date.

**Combine COUNT with other functions.** If you want to know how many cells are populated at all, compare COUNT with COUNTA. If you want a total of the numeric values, use the SUM function on the same range. If you want to count numbers that pass a test, such as values above 10, use COUNTIF. To add up those values instead, use SUMIF and SUMIFS.

## Errors and How to Fix Them

COUNT rarely throws an error because it simply skips anything it cannot count. The problems usually show up as a wrong number instead of an error message.

**The count is lower than expected.** Some cells that look numeric are actually text. A number stored as text, such as a value imported with a leading apostrophe, is not counted when it sits in a range [1]. Fix it by converting the text to numbers, for example with the VALUE function or by retyping the entry.

**The count is higher than expected.** Dates and times are numbers, so they are counted [1]. If your range mixes dates with measurements, COUNT includes both. Separate the columns or use COUNTIF with a criteria that matches only the values you want.

**An error appears.** COUNT itself does not return an error for error values in its arguments, because it skips them [1]. If a formula built around COUNT shows `#VALUE!` or `#N/A`, the error comes from another part of the formula, so check those parts and the cells they reference.

**The result is 0.** Either the range truly has no numbers, or every entry is text. Test with COUNTA on the same range. If COUNTA returns a number and COUNT returns 0, the cells hold text, not numbers.

**The range is wrong.** A common cause of a bad count is a range that does not cover all the data, such as `B2:B13` when the data runs to row 20. Widen the range or use a full-column reference.

## Common Mistakes

- **Using COUNT to count all non-empty cells.** COUNT ignores text and blanks, so it undercounts. Use COUNTA when you want every populated cell [1].
- **Expecting COUNT to apply criteria.** COUNT has no criteria argument. Use COUNTIF for one condition or COUNTIFS for several [1].
- **Assuming text that looks like a number will count inside a range.** Only numbers, dates and text representations of numbers typed directly into the arguments are counted. Text in a referenced cell is not counted [1].
- **Forgetting that dates count as numbers.** A date column will inflate your count if you mix it with other data [1].
- **Ignoring error values in the range.** Error values are not counted, so a column full of `#N/A` results can return a misleadingly low number [1].
- **Hard-coding values instead of referencing cells.** Typed literals are counted even when they are text representations of numbers, which can hide data problems in the sheet [1].

## Limitations

COUNT answers one narrow question: how many numeric entries are present. It cannot tell you whether those numbers are correct, complete or in the right format. A cell holding `0` counts the same as a cell holding `1000000`, so COUNT is a volume check, not a quality check.

COUNT also cannot filter by condition, and it cannot distinguish between a genuine number and a date or time, since all three are stored as numbers [1]. When you need conditional counting, text counting or error counting, reach for COUNTIF, COUNTIFS or COUNTA instead [1]. For counting rows in a database query rather than a spreadsheet, the SQL COUNT function follows similar logic with its own rules.

## Frequently Asked Questions

### What is the difference between COUNT and COUNTA?

COUNT counts only cells that contain numbers [1]. COUNTA counts all non-empty cells, including text, logical values and error values [1]. Use COUNT when you care about numeric data and COUNTA when you care about whether a cell has anything in it at all.

### Does COUNT count blank cells?

No. Empty cells in a range or array are not counted [1]. If a cell contains a space character, it is not truly empty and COUNTA would count it, but COUNT still would not count it as a number.

### Does COUNT count text that looks like a number?

It depends where the text sits. A text representation of a number typed directly into the argument list, such as `"1"`, is counted [1]. The same text stored in a referenced cell is not counted [1]. This difference trips up many people who import data from other systems.

### Can COUNT count numbers that meet a condition?

No. COUNT has no criteria argument. To count only numbers that meet certain criteria, use the COUNTIF function or the COUNTIFS function [1]. For example, COUNTIF can count values above a threshold or within a range.

### Why does COUNT return a different number than I expect?

The usual causes are text stored as numbers, dates counted as numbers, or a range that does not cover all your data [1]. Compare COUNT with COUNTA on the same range to see how many cells are populated, then inspect the difference to find the text or blank entries.

## References

1. [COUNT function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/count-function)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)

## Related Articles

- [COUNTIF Function in Excel: Syntax and Examples](/blog/data-analysis/countif-function-excel)
- [Excel SUM Function: Syntax, Examples and Tips](/blog/data-analysis/excel-sum-function-examples)
- [Excel FIND Function: Syntax, Examples and SEARCH Comparison](/blog/data-analysis/excel-find-function-syntax-examples)
- [Excel INDEX Function: Syntax, Examples and How to Use It](/blog/data-analysis/excel-index-function-syntax-examples)
- [SQL COUNT Function: Syntax, Examples and GROUP BY](/blog/data-analysis/sql-count-function)