COUNT Function in Excel: Examples and Common Uses

To count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and genuine Excel dates, but ignores text, blank cells, and numbers stored as text in a referenced range.

What the COUNT function does

COUNT is a built-in Excel function for finding how many cells in a selection contain numeric values. Its basic syntax is COUNT(value1, [value2], ...): value1 is required, and you can add further cells or ranges as optional arguments. In everyday worksheets, the most common form is a single range, such as a column of prices, quantities, scores, or dates.

For example, suppose B2:B10 contains 125, a blank cell, the word Pending, 0, a genuine date, 8.5, the text “42” stored as text, another blank, and -3. COUNT returns 5: it counts 125, 0, the date, 8.5, and -3. Zero and negative numbers still count. The date counts because Excel stores genuine dates as serial numbers; the cell’s date format only changes how that number is displayed.

COUNT answers a specific question: “How many numeric entries are here?” It does not measure how many cells are filled, how many records meet a condition, or how many distinct values exist. Choosing the right question first prevents many spreadsheet counting errors.

Basic COUNT examples

Use COUNT on the cells that contain the values you want to inspect. These examples assume Excel for Microsoft 365 on Windows, though the formulas also work in current Excel for Mac and Excel for the web.

Count numbers in one column: With scores in B2:B20, =COUNT(B2:B20) returns the number of numeric score cells. If 16 cells contain numbers and the rest are blank or text, the result is 16.
Count numbers across separate ranges: With January figures in B2:B10 and February figures in D2:D10, =COUNT(B2:B10,D2:D10) counts numeric entries in both ranges. If the first range has 8 numbers and the second has 7, the result is 15.
Count a single cell: With a possible numeric entry in C5, =COUNT(C5) returns 1 if C5 contains a number or genuine date, and 0 if it is blank or contains ordinary text.

In all three cases, COUNT evaluates cell contents rather than appearance. A cell displaying a currency symbol may still contain a number and count; a cell displaying “100” may not count if its actual content is text. Formatting alone does not reliably tell you which kind of value Excel has stored.

Counting numbers that meet a condition

COUNT has no built-in criteria argument. When the goal is to count numbers that satisfy a rule, use COUNTIF for one condition or COUNTIFS for several. In both functions, the criteria can be a comparison such as greater than zero, or text that must match.

Count positive amounts: With numeric amounts in B2:B20, =COUNTIF(B2:B20,">0") counts values greater than zero. If seven cells meet that rule, it returns 7. Zero and negative values do not meet it.
Count entries equal to a word: With status labels in C2:C20, =COUNTIF(C2:C20,"Complete") counts cells matching Complete. COUNTIF is not case-sensitive, so it does not distinguish Complete from COMPLETE.
Count records meeting two rules: With sales figures in B2:B20 and regions in C2:C20, =COUNTIFS(B2:B20,">=50",C2:C20,"West") counts rows where the sales figure is at least 50 and the region is West. If three rows meet both conditions, the result is 3.

For a comparison, COUNTIFS applies all supplied criteria together: a row must satisfy every condition to be included. The criteria ranges should have matching dimensions so each sales figure is evaluated alongside the region on the same row. For a count based on only one criterion, COUNTIF is simpler and easier to maintain.

COUNT compared with COUNTA and COUNTBLANK

COUNT, COUNTA, and COUNTBLANK answer different questions. They are not interchangeable, even though all three can take a range as an argument.

COUNT counts numeric values, including genuine dates. It ignores ordinary text and blank cells in a referenced range.
COUNTA counts cells that are not empty. It includes text, numbers, dates, and formulas that return an empty text string, so a cell that looks blank may still be counted.
COUNTBLANK counts blank cells, including cells containing a formula that returns an empty string. It does not count cells containing a space character as blank.

For instance, with an employee list in A2:A20, COUNTA is generally more suitable if you want to count names, since names are text. With numeric hours in B2:B20, COUNT is suitable if text notes should not be treated as recorded hours. With a form response range where unanswered cells matter, COUNTBLANK may be the relevant measure. If the count looks surprising, compare these functions to identify whether text, formulas, or genuinely empty cells are affecting the result.

Numbers stored as text and other common problems

A frequent reason COUNT returns less than expected is that some entries are numbers stored as text. This can happen when data is imported from another application, copied from a website, or entered with a leading apostrophe. The cell may display “42,” but Excel treats it as text, so COUNT ignores it when it appears in a referenced range.

Check a questionable cell by selecting it and looking at the formula bar; a leading apostrophe is one clue. You can also use the warning icon Excel may show beside a cell, or test the value with ISNUMBER. If the text is meant to be numeric, convert it using an appropriate method, such as selecting the affected cells and choosing the warning menu’s Convert to Number option when that option is available. For a large or recurring import, Power Query or a carefully designed conversion formula may be more consistent.

Other issues to check include:

Numbers outside the selected range: Confirm the formula covers the full data area, including newly added rows.
Dates that are text: A date-looking string imported as text is not a genuine Excel date and will not be counted by COUNT.
Formula results: A formula returning a number counts; a formula returning text, including an empty string, does not count as a number.
Hidden or filtered rows: COUNT normally includes numeric cells in hidden and filtered-out rows. If you need a count that changes with filtering, COUNT is not the right tool by itself.
Cells containing errors: An error value is not a numeric entry that COUNT can count. Resolve or account for errors separately if they indicate missing or invalid data.

Do not convert every text value automatically if the column intentionally mixes IDs and numbers. For example, a product code such as “00125” may need to remain text to preserve its leading zeros. Decide what the data represents before changing its type.

Counting visible rows in a filtered list

COUNT includes numbers in rows hidden by a filter, so filtering a table does not make a basic COUNT result show only visible records. This is a common source of confusion when a worksheet displays a handful of rows but the total seems much larger.

For counts that respond to filtering, SUBTOTAL is often a better starting point. With numeric values in B2:B20, =SUBTOTAL(2,B2:B20) counts numeric cells in the visible rows and ignores rows excluded by a filter. Function number 2 specifies COUNT behavior. If the result also needs to ignore manually hidden rows, use function number 102 instead: with the same range, =SUBTOTAL(102,B2:B20) counts visible numeric cells while excluding manually hidden rows as well as filtered-out rows.

SUBTOTAL’s behavior depends on the function number selected, so check whether manual hiding matters for your task. It also does not count text-only records; if the visible-row question concerns names rather than numbers, select a suitable counting approach for that data type.

Using COUNT in a larger worksheet

COUNT is useful as a building block in reports and data checks. A count of numeric entries can indicate how many survey responses have been recorded, how many transactions have an amount, or how many rows contain a score. It can also be compared with the expected number of records to flag incomplete data, provided the chosen range and data type match the question.

For example, if a worksheet has 25 expected test scores but COUNT returns 22, three cells may be blank, contain text, or hold numbers stored as text. The result does not identify which cells are responsible, so inspect the range or use filters and validation rules to find the missing entries. Likewise, a count alone does not prove the data is accurate: a numeric value in the wrong row still increases the total.

When setting up a worksheet for repeated use, keep the data in a clearly defined column and avoid mixing notes with numeric entries. Excel Tables can make ranges easier to extend as rows are added. In a formula that refers to a table column, the structured reference can make the intent clearer, although the core COUNT behavior remains the same: only numeric values are included.

Frequently Asked Questions

Does COUNT include zero?
Yes. Zero is a numeric value, so COUNT includes it. Negative numbers and decimals also count.

Does COUNT count dates in Excel?
Yes, if the cells contain genuine Excel dates. Excel stores dates as numbers, so COUNT includes them even when the cells are formatted to display dates.

Why does COUNT ignore a number that looks correct?
The value may be stored as text rather than as a number. This commonly happens with imported data or entries prefixed by an apostrophe. Convert it only if the value is intended to be numeric.

How do I count nonblank cells instead?
Use COUNTA when you want to count cells containing any value, including text. Remember that formulas returning an empty string can still be counted by COUNTA even though they look blank.

Does COUNT change when I filter a range?
No. COUNT includes numeric cells in filtered-out rows. Use SUBTOTAL when the count should reflect visible rows, choosing the function number that matches whether manually hidden rows should be included.

Can COUNT count cells that meet a condition?
COUNT itself cannot take a criterion. Use COUNTIF for one condition, such as a value greater than zero, and COUNTIFS when several conditions must be true.

Choosing the right counting function

Start by deciding what should count as an entry. Choose COUNT for numeric values, including real dates; COUNTA for cells that are not empty; COUNTBLANK for empty cells; COUNTIF or COUNTIFS for criteria; and SUBTOTAL when filtering should affect the visible-row count. Then check whether the source data has the expected type and whether hidden rows should be included. That simple distinction makes COUNT formulas easier to interpret and helps prevent a plausible-looking total from answering the wrong question.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.