COUNTIF in Excel: Count Cells That Contain Numbers

Yes—COUNTIF can count cells that meet a numeric condition, and it can find text containing a particular digit. But if you simply want to count cells whose values are numbers, use COUNT instead: with values in A2:A20, enter =COUNT(A2:A20). The right formula depends on whether “contains number” means a numeric value, a number that passes a test, or text with a digit in it.

Three meanings of “contains a number”

The wording can describe three different spreadsheet tasks. Distinguishing them first prevents a formula from returning a plausible but incorrect count:

Cells holding numeric values: Count entries Excel recognizes as numbers, such as 12, -4.5, or a date. COUNT is the direct choice.
Numbers meeting a condition: Count numeric values above, below, or equal to a threshold. COUNTIF is designed for one condition; COUNTIFS handles multiple conditions.
Text containing a digit: Find text such as “Order 7” or “Room 204.” A wildcard COUNTIF can find a specific digit in text, but detecting any digit requires a different approach.

These categories matter because a cell that displays digits is not necessarily a numeric value. For example, an imported ID like “007” may be stored as text so that its leading zeros remain. COUNT ignores that entry, while a text search can find its digits.

Count cells that contain numeric values

With values in A2:A20, use =COUNT(A2:A20). COUNT returns the number of cells in the range that Excel recognizes as numeric; it does not add the numbers together. If the range contains 14, a date, the text “23,” and a blank, the result is 2: 14 and the date count as numbers, while the text and blank do not.

Excel stores dates and times as serial numbers, so COUNT includes them. It also counts numbers returned by formulas, but not formulas that return text such as an empty string. A cell containing the text “23” remains text even though it looks numeric. You can check a suspicious cell with =ISNUMBER(A2); with the value in A2, the result is TRUE for a numeric value and FALSE for text that looks like one.

COUNT is usually preferable to trying to build a numeric-type test out of COUNTIF conditions. Criteria such as “greater than or equal to zero” answer a comparison question, not necessarily the more precise question of whether every cell is stored as a number. COUNT directly expresses the latter.

Count numbers that meet a condition with COUNTIF

COUNTIF is useful when you want a numeric count with a rule. Its general syntax is =COUNTIF(range,criteria). The range is where Excel looks, and the criteria specifies what qualifies. For example, with scores in B2:B20, use =COUNTIF(B2:B20,">=70") to count scores of 70 or higher. If the qualifying scores are 70, 82, and 95, the result is 3.

Comparison operators in criteria must be enclosed in quotation marks. Common operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal to (<>), and equal to (=). A criterion can also refer to a cell: with a threshold in D1 and scores in B2:B20, use =COUNTIF(B2:B20,">="&D1). The ampersand joins the comparison operator to the value in D1.

For a simple exact match, with status labels in C2:C50, use =COUNTIF(C2:C50,"Complete"). COUNTIF is not case-sensitive, so it treats “Complete” and “complete” as matches. If a condition needs to be combined with another condition—for example, a score threshold and a department—COUNTIFS is generally the appropriate function.

Find a specific digit in text

COUNTIF supports the wildcard asterisk, which represents any number of characters. With text descriptions or IDs in A2:A100, use =COUNTIF(A2:A100,"*5*") to count text entries containing the digit 5 anywhere. The asterisks allow any characters before or after the 5, so values such as “Box 5,” “A51,” and “105B” match. This is a substring search, not a numeric comparison.

Wildcards are not a reliable way to ask whether a cell is a numeric value, and searching for “5” does not mean “is greater than 5” or “equals 5.” Use a comparison criterion for numeric rules. Also note that COUNTIF’s wildcard syntax does not provide a bracket expression such as [0-9] to mean “any digit.” A criterion like =COUNTIF(A2:A100,"*[0-9]*") should not be used as a digit-range pattern; Excel COUNTIF wildcards are based on asterisk, question mark, and tilde escaping.

Detect any digit in a text cell

If you need to identify text containing at least one digit from 0 through 9, a helper column makes the test transparent. With the text to inspect in A2, enter =SUMPRODUCT(--ISNUMBER(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2)))>0 in B2. It returns TRUE if SEARCH finds any of the ten digit characters and FALSE if it finds none. Copy the formula down for the other rows.

Then, with the TRUE/FALSE results in B2:B20, count matching rows using =COUNTIF(B2:B20,TRUE). For example, if A2:A5 contains “Room 4,” “North,” “Unit 12,” and “West,” the helper returns TRUE, FALSE, TRUE, FALSE, and the count is 2. SEARCH is not case-sensitive, which is immaterial for digits, and the formula checks for digits inside text rather than testing whether the original cell has a numeric data type.

This helper approach is useful when the digit could appear anywhere in a description. If you only need to find one known digit, COUNTIF with a wildcard is shorter. If you need to validate an entire code format—such as exactly five digits—define that more specific rule rather than treating any digit as sufficient.

Numeric text, blanks, and other common traps

Imported data often mixes numbers and text. A column might contain the numeric value 25 in one row and the text value “25” in another. COUNT counts the first and ignores the second. A wildcard search is intended for text-pattern matching, while COUNTIF comparison criteria are intended for conditions; neither should be mistaken for a universal test of the underlying cell type.

To convert numeric text to actual numbers, first make a copy or confirm the conversion is appropriate. In many worksheets, selecting the affected cells and using the warning icon’s Convert to Number option works when Excel flags numbers stored as text. You can also use a conversion formula in a separate column, then copy the results and paste values if needed. Be cautious with codes, account numbers, and postal codes: converting these can remove meaningful leading zeros or change how the data should be treated.

Blanks are another reason to choose a formula based on the intended meaning. COUNT ignores blank cells, while a condition-based count asks whether cells meet a specific comparison. If formulas return an empty string, they may look blank but contain a formula. Test the result you actually need, especially when a range includes formulas, imported values, or mixed data types.

Choosing the right formula

Use the simplest formula that matches the question. A useful decision guide is:

“How many cells contain numbers?” Use =COUNT(A2:A20), assuming the values to inspect are in A2:A20.
“How many values are at least 100?” Use =COUNTIF(A2:A20,">=100"), assuming the values are in A2:A20.
“How many text entries contain the digit 5?” Use =COUNTIF(A2:A20,"*5*"), assuming the entries are in A2:A20 and the target is the digit 5 in text.
“How many text entries contain any digit?” Use the helper-column test, then count its TRUE results.

COUNTIF is available in Microsoft 365 for Windows, as well as other modern Excel editions. The formulas above use standard Excel syntax with commas between arguments. On a computer configured to use a different list separator, Excel may display or require semicolons instead. The formulas and ribbon features described here do not depend on a Windows-only function.

COUNTIF compared with COUNT and COUNTIFS

COUNT, COUNTIF, and COUNTIFS are related but answer different questions. COUNT counts numeric entries without applying a condition. COUNTIF checks one range against one criterion, which might be a text match or a numeric comparison. COUNTIFS checks multiple criteria, usually across corresponding ranges, and counts rows where all specified conditions are met.

For example, if scores are in B2:B20 and names of teams are in C2:C20, COUNTIF can count all scores of at least 70. To count only scores of at least 70 for the “Blue” team, use COUNTIFS: with scores in B2:B20 and team names in C2:C20, enter =COUNTIFS(B2:B20,">=70",C2:C20,"Blue"). Both conditions must be true on the same row. Use COUNT rather than COUNTIF when there is no condition, and use a text-search method when the criterion concerns digits embedded within text.

Frequently Asked Questions

Can COUNTIF count cells that contain any number?
Not with a single standard wildcard criterion that means “any digit.” For cells containing numeric values, use COUNT. For text that may contain any digit, use a digit-search helper formula and count its TRUE results.

How do I count numbers greater than zero?
With the values in A2:A20, use =COUNTIF(A2:A20,">0"). This counts values that satisfy the greater-than-zero condition; it does not count zero or negative values.

Does COUNT include dates?
Yes. Excel stores dates and times as numeric serial values, so COUNT includes cells containing dates or times that Excel recognizes as such.

Why does COUNT ignore a cell that looks like a number?
The cell may contain text, often because the value was imported or entered with a leading apostrophe. COUNT ignores text. Check it with ISNUMBER, and convert it only if the value should genuinely be numeric.

Can COUNTIF find a digit anywhere in a text entry?
Yes, for a particular digit. With text in A2:A100, =COUNTIF(A2:A100,"*7*") counts text entries containing 7. To find any digit from 0 to 9, use the helper-column formula instead.

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.