Excel COUNT Formula Examples: Numbers, Blanks, and Text

In Excel for Microsoft 365, use =COUNT(A2:A20) to count cells containing numbers, including dates and times. For other kinds of counts, use COUNTA for nonblank cells, COUNTBLANK for empty cells, COUNTIF for one condition, or COUNTIFS for multiple conditions.

What COUNT counts in Excel

COUNT is for numerical values, not for every cell that looks filled. It counts numbers in the referenced cells, including dates and times, because Excel stores dates and times as numbers. It does not count ordinary text, logical values such as TRUE, or blank cells in a referenced range. Errors in the range are not counted as numbers.

For example, imagine A2:A7 contains the number 12, the date 11/3/2026, the text “North,” a blank cell, TRUE, and the text “18.” COUNT returns 2: the number and the date. The text “18” may look numeric, but when it is stored as text in a cell in the referenced range, COUNT ignores it. This distinction often explains why a count is lower than expected.

COUNT can take more than one range or value as an argument. In everyday worksheets, a single range is usually clearest. If a cell contains a formula that returns a number, COUNT counts that result; if the formula returns text, it does not.

Basic COUNT formula examples

With numeric entries in a range, COUNT provides a quick total of the cells containing numbers. These examples assume that data starts in row 2, leaving row 1 for headings.

With exam scores in B2:B31, =COUNT(B2:B31) returns the number of scores entered as numbers. If 27 of the 30 cells contain numeric scores, the result is 27.
With dates in C2:C100, =COUNT(C2:C100) returns the number of cells containing valid Excel dates. If 84 cells contain dates and the rest are blank or text, the result is 84.
With sales figures in B2:B20 and D2:D20, =COUNT(B2:B20,D2:D20) counts numeric entries across both ranges. If the first range has 15 numbers and the second has 12, the result is 27.

Each example uses the stated cell layout: the score range is B2:B31, the date range is C2:C100, and the two sales ranges are B2:B20 and D2:D20. Check that values imported from another system are genuine numbers rather than numbers stored as text. Depending on the source, you can convert text numbers to numbers or correct the import before relying on COUNT.

COUNT, COUNTA, and COUNTBLANK compared

These three functions answer different questions. Choosing the one that matches the question is more important than changing the range. For a list in A2:A10 containing four numbers, two text entries, one formula returning an empty string, and two truly empty cells, the results differ:

With the described contents in A2:A10, =COUNT(A2:A10) returns 4 because only the four numeric values qualify.
With the same contents in A2:A10, =COUNTA(A2:A10) returns 7. COUNTA counts cells containing data, including text and a formula, even if that formula displays an empty string.
With the same contents in A2:A10, =COUNTBLANK(A2:A10) returns 3: the two truly empty cells and the cell whose formula returns an empty string. COUNTBLANK treats a formula result of empty text as blank.

COUNTA is useful when you want to count populated records, but it does not determine whether the values are meaningful or valid. A cell containing a space, an error, or a formula that displays nothing can still affect your interpretation of “filled.” COUNTBLANK is useful for finding missing entries, though its treatment of formulas that return empty text means it may report more blanks than are physically empty cells.

Count cells that meet one condition with COUNTIF

COUNTIF counts cells in one range that meet a criterion. The criterion can be a number, text, comparison, or wildcard pattern. Text criteria go in straight double quotes; comparison operators such as greater-than signs are also written inside quotes when used directly.

With status labels in C2:C50, =COUNTIF(C2:C50,"Complete") counts cells whose value is Complete, without regard to letter case. If 32 cells contain that label, the result is 32.
With quantities in B2:B20, =COUNTIF(B2:B20,">10") counts values greater than 10. If seven quantities are greater than 10, the result is 7; values equal to 10 are not included.
With names in A2:A30, =COUNTIF(A2:A30,"M*") counts text entries beginning with M. The asterisk matches any number of characters, including none.

For each example, the assumed data range is the one named in the sentence: C2:C50 for statuses, B2:B20 for quantities, and A2:A30 for names. COUNTIF criteria are flexible, but a criterion referring to a cell needs to be joined to a comparison operator. For instance, with a threshold in E2 and values in B2:B20, =COUNTIF(B2:B20,">"&E2) counts values greater than the threshold. If E2 contains 10 and seven values exceed it, the result is 7.

Count rows that meet multiple conditions with COUNTIFS

Use COUNTIFS when each counted row must satisfy two or more conditions. Each criteria range must be the same size and shape as the others. Excel tests the conditions on corresponding positions, so a row is counted only when all supplied conditions are true.

With regions in A2:A100 and order values in B2:B100, =COUNTIFS(A2:A100,"West",B2:B100,">=500") counts rows where the region is West and the order value is at least 500. If 6 rows meet both conditions, the result is 6.
With dates in A2:A100 and statuses in C2:C100, =COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2027,1,1),C2:C100,"Paid") counts rows dated in 2026 with a Paid status. If 41 rows meet all three conditions, the result is 41.

The first example assumes both ranges run from row 2 through row 100; the second assumes dates are in A2:A100 and statuses in C2:C100. The date example uses a start date inclusive and the next year’s start date exclusive. This pattern avoids ambiguity about times included in date cells: a timestamp on December 31, 2026 is still earlier than January 1, 2027. If your data uses a different year, change the dates in the formula.

Count text, partial matches, and exclusions

COUNTIF can count text patterns, but wildcards have specific meanings: an asterisk matches any sequence of characters, and a question mark matches one character. A tilde can be used to treat a wildcard character literally. For example, to count a literal asterisk in a text value, the criterion uses a tilde before the asterisk.

With product names in A2:A40, =COUNTIF(A2:A40,"*tea*") counts text entries containing “tea” anywhere in the cell. It can match values such as “Green tea” and “teapot.”
With codes in A2:A40, =COUNTIF(A2:A40,"AB??") counts entries that begin with AB and have exactly two further characters matched by the question marks.
With status values in C2:C50, =COUNTIF(C2:C50,"<>Cancelled") counts cells whose value is not Cancelled, but be cautious: this criterion can also include blank cells.

In the third example, the assumed range is C2:C50, and the result is not necessarily the number of nonblank statuses. If you need to exclude blanks as well as Cancelled, add a second condition with COUNTIFS: with statuses in C2:C50, =COUNTIFS(C2:C50,"<>Cancelled",C2:C50,"<>") counts cells that are both not Cancelled and not blank. If 35 cells meet both conditions, the result is 35. In HTML, the less-than character is written as < so the displayed formula shows the Excel criterion correctly.

Common COUNT formula problems

If the result is unexpectedly low, first check whether numbers are stored as text. A green error indicator may appear in some cells, or the values may be left-aligned depending on formatting and settings, but neither clue is conclusive. Changing a cell’s number format alone does not convert text into a number. Use an appropriate conversion method, such as Text to Columns or a conversion formula, and verify a few cells before counting the full range.

Other frequent causes include:

Wrong function: COUNT ignores text. Use COUNTA to count cells containing entries of different types, or COUNTIF to count a particular text value.
Unexpected formula results: COUNT ignores formulas returning text, while COUNTA counts a formula even when it displays an empty string. COUNTBLANK counts formulas returning empty text.
Range excludes new rows: A formula such as =COUNT(B2:B20) will not include data entered in B21. Expand the range or use an Excel Table so formulas can use a column that grows with the table.
Criteria mismatch: Extra spaces or inconsistent labels can prevent an exact text criterion from matching. Check the source values and standardize them if needed.
Locale differences: This article uses comma argument separators, as requested for standard English-language Excel syntax. Some regional Excel settings display semicolons instead.

When investigating a discrepancy, test a small range and inspect the actual cell contents and formulas. Also confirm whether the goal is to count numeric measurements, completed records, missing values, or rows meeting a business rule; those are different questions and may need different functions.

How to choose the right counting formula

Start by defining what qualifies as one item. Then choose the simplest function that expresses that definition:

Count numeric values, including date serials: COUNT.
Count cells containing something, whether number or text: COUNTA.
Count empty cells, including formulas returning empty text: COUNTBLANK.
Count entries matching one rule: COUNTIF.
Count rows matching several rules at once: COUNTIFS.

For a recurring report, consider converting the data range to an Excel Table using Insert > Table. Tables expand when rows are added, and formulas that refer to table columns can be easier to read than fixed cell addresses. For instance, a table column called Amount can be referenced by its column name in a structured reference. Keep headers clear and avoid mixing notes or subtotals into the data rows, since inconsistent layouts make counts harder to interpret.

Frequently Asked Questions

How do I count cells containing numbers in Excel?
Use COUNT with the range. With numbers in B2:B20, =COUNT(B2:B20) returns the number of cells in that range containing numeric values, including valid Excel dates.

Does COUNT include dates?
Yes. Excel stores dates and times as serial numbers, so COUNT includes valid date and time values in a referenced range. Text that merely looks like a date is not necessarily counted.

How do I count nonblank cells?
Use COUNTA for cells containing entries of any type. Remember that a formula returning an empty string is still a formula in the cell and is counted by COUNTA.

How do I count blank cells, including formulas that display nothing?
Use COUNTBLANK. It counts genuinely empty cells and cells containing formulas that return an empty string, so its result can differ from a count of physically untouched cells.

Can COUNTIF count cells containing part of a word?
Yes. Use an asterisk on either side of the text, such as =COUNTIF(A2:A40,"*tea*") for entries containing “tea.” The asterisk matches any sequence of characters.

Why does COUNT return zero when cells look like numbers?
The values may be stored as text rather than numbers. Convert them to numeric values, then recalculate or check the result. Applying a number format alone changes appearance, not the underlying text value.

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.