Excel COUNT Function

COUNT tells you how many cells in a range contain a number. It ignores text, blank cells, and logical values when they come from a cell reference, but the same values are treated differently when typed straight into the formula. That inconsistency trips up more people than any other part of this function, so we'll walk through it in detail below.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and earlier versions. Cross-sheet references like Jan!C2:C50 and 3D references like Jan:Dec!C2:C50 work the same way in every version listed.

Syntax

=COUNT(value1, [value2], ...)

Parameter	Required	Description
`value1`	Yes	The first cell, range, or number to count. Can be a single cell reference, a full range, or a number typed directly into the formula.
`value2, ...`	No	Additional cells, ranges, or numbers to include in the same count. COUNT accepts up to 255 arguments total.

Basic Example

You're tracking order amounts for a sales team. Column C holds the dollar value of each order, but a few rows are still blank because the order hasn't shipped, and a few say "Pending" instead of a number.

=COUNT(order_amounts)

// order_amounts = C2:C15, the range holding each order's dollar value

This returns the number of rows in that range that actually contain a dollar figure. Blank rows don't count. Rows that say "Pending" don't count either, because COUNT only recognizes numeric values, not text that describes a number.

How COUNT Works

What counts as a number

COUNT recognizes numbers, dates, times, percentages, and fractions. It also counts formulas that return a number, even if the formula displays as a date or currency format on screen. Underneath, Excel stores dates and times as serial numbers, which is why COUNT treats a column of order dates exactly like a column of order totals.

Text and blank cells are ignored, but only inside references

If value1 is a cell reference or a range, COUNT skips blank cells, text entries, and logical values entirely. A cell containing the word "N/A" or "Cancelled" doesn't add to the count. Neither does an empty cell.

⚠

Type a value directly into the formula and the rules change. =COUNT(5, "5", TRUE) returns 3, because a number stored as text and a typed logical value both count when entered inline. The exact same values sitting in cells B2 and B3 would return 1 or 2 depending on their type. This is the single most common source of "COUNT is giving me the wrong number" complaints.

Errors inside a range don't stop COUNT

Most math functions choke on an error value and return that error to you. COUNT doesn't. If C7 contains #DIV/0! and the rest of the range is numbers, =COUNT(C2:C15) still returns a clean count, simply skipping the error cell like it would skip a blank one. That behavior is worth knowing, because SUM and AVERAGE won't extend you the same courtesy.

Multiple ranges, including across sheets

COUNT accepts more than one argument, so you can count several ranges in a single formula without stacking them into one continuous block.

ℹ

A non-contiguous range just means two or more separate blocks of cells, referenced together in one formula instead of one unbroken rectangle.

=COUNT(C2:C15, F2:F15)   // adds the numeric counts from two separate columns

This works across sheets too. To count numeric order amounts entered in three monthly tabs:

=COUNT(Jan!C2:C50, Feb!C2:C50, Mar!C2:C50)

Each sheet reference is counted separately, then summed into one total. No helper column required.

Common Use Cases

Spotting missing data entry

If you know a range should have 200 rows filled in, compare COUNT against the row total to find gaps fast.

=COUNT(order_amounts) - 200   // negative result means rows are still missing values

Guarding against a divide-by-zero error

Before averaging a column that might be entirely blank or entirely text, check that it actually contains numbers first.

=IF(COUNT(scores)=0, "No scores yet", AVERAGE(scores))

Counting numbers across a full workbook of monthly tabs

A 12-tab sales workbook, one tab per month, each with the same layout. Instead of copying a formula 12 times, reference every sheet in one line.

=COUNT(Jan:Dec!C2:C50)

// Jan:Dec = every sheet from Jan through Dec
// C2:C50  = the same order amount range on each of those sheets

Counting numeric entries in a survey column

Respondents typed either a rating from 1 to 10 or left the field blank. COUNT tells you how many people actually responded, separate from how many rows exist.

=COUNT(survey_ratings)   // ignores blank rows where no rating was given

Handling Errors

COUNT itself is one of the more forgiving functions in Excel. It's built to skip past text, blanks, and errors inside a range rather than choke on them, so a genuine error from COUNT is rare. When you do see one, it's almost always #REF!, and it points to a broken reference, not a problem with COUNT's logic.

Common causes of #REF!:

A row or column inside the referenced range was deleted
A sheet named in a cross-sheet formula was renamed or removed
The range was copied to a location where part of it no longer exists

=IFERROR(COUNT(Jan!C2:C50, Feb!C2:C50), "Check sheet references")
// This IFERROR only catches a broken reference, like a renamed or deleted sheet.
// A genuine 0 count (no numbers found) is not an error and passes through untouched.

✓

Don't wrap COUNT in IFERROR to catch a "wrong" result of 0. A count of 0 is a valid answer, it means no numbers were found, not that the formula failed. Save IFERROR for actual broken references.

Notes & Gotchas

What is the COUNT function used for in Excel?

COUNT tells you how many cells in a selected range contain a number. It's most often used to check that a column of data was actually filled in with numeric values before running a calculation like SUM or AVERAGE on top of it.

What is the difference between COUNT and COUNTA in Excel?

COUNT only counts numbers, dates, and times. COUNTA counts everything that isn't blank, including text, logical values, and error values. If you need to know how many rows have any entry at all, use COUNTA; if you only care about the numeric ones, use COUNT.

How do you count cells with specific criteria in Excel?

COUNT has no built-in way to filter by condition, it counts everything numeric in the range. For conditional counting, like "how many orders exceed $500," use COUNTIF with a criteria argument such as =COUNTIF(order_amounts, ">500"). COUNTIFS extends the same idea to multiple conditions at once.

Why does the COUNT function sometimes not count certain cells?

The most common cause is a number stored as text, which looks identical to a real number but sits left-aligned in the cell and gets skipped by COUNT when referenced from a range. Blank cells and cells with any text, including a single space, are also excluded. Cells containing error values are excluded as well, even though they might visually look like they belong in the count.

How many arguments can the COUNT function accept?

COUNT accepts up to 255 arguments in one formula. Each argument can be a single value, a cell reference, or an entire range, so in practice you can cover far more than 255 individual cells by grouping them into ranges.

Does COUNT throw an error when a range contains #DIV/0! or #N/A?

No. COUNT silently ignores error values found inside a referenced range and continues counting the numeric cells around them. This is different from SUM or AVERAGE, both of which propagate the error to the entire formula result.

Does COUNT work with dates and times?

Yes. Excel stores dates and times as serial numbers behind the scenes, so COUNT treats a column of dates exactly like a column of numbers. A formula like =COUNT(order_dates) returns the number of cells holding a valid date, skipping any blank or text entries like "TBD."

Related Functions

Function	Use this when...
`COUNTIF`	You need to count cells that meet a single condition, like values above a threshold.
`COUNTIFS`	You need to count cells that meet more than one condition at the same time