Excel COUNTIF for Numbers in Cells: Examples and Fixes

Formulas & functions · Updated November 19, 2026

To count cells that equal a number in Excel, use =COUNTIF(B2:B20,25), replacing the range and 25 with your own. For a number stored in another cell, use =COUNTIF(B2:B20,D1). COUNTIF can also count numbers above or below a limit, while the simpler COUNT function is usually better when you want to count every numeric cell.

What “number in a cell” can mean

The question “Excel COUNTIF number in cell?” can describe a few different tasks. You might want to count cells equal to a particular number, count values that meet a threshold, count every cell containing a number, or find text that includes a digit. These tasks use different criteria, so it helps to decide what you mean before writing a formula.

COUNTIF counts cells in one range that meet one condition. The basic structure is =COUNTIF(range,criteria). The range is the cells Excel checks; the criteria tell Excel what qualifies. A criterion can be a number, text, a comparison such as greater than 10, or a wildcard pattern. The formula returns a count, not the values themselves.

One important distinction: a cell’s appearance is not necessarily its stored value. A cell formatted to display two decimal places might contain 12.345, and a cell formatted as a date contains a numeric date serial value. COUNTIF evaluates the underlying value against its criterion, not merely what the cell looks like on screen.

Count cells equal to a specific number

For a direct match, enter the number as the second argument. Suppose B2:B9 contains 25, 100, 120, 150, a blank, 100, 80, and 25. The formula below returns 2 because two cells equal 25.

With the values listed in B2:B9, use this formula to count cells equal to 25: =COUNTIF(B2:B9,25). The result is 2.

If the target number is in a worksheet cell, refer to that cell instead. This is useful when you want to change the target without editing the formula. For example, put 25 in D1.

With values in B2:B9 and the target number in D1, use =COUNTIF(B2:B9,D1). It returns 2 when D1 contains 25. Do not put quotation marks around a cell reference; quotation marks would make it text rather than a reference.

For a whole-number match, the direct number criterion is usually clearest. If your data contains calculated decimal values, remember that two values that display alike may not be precisely equal underneath. If a calculation produces a value close to 25 but not exactly 25, an exact-match count may not include it. Decide whether you need exact equality or a tolerance around the target.

Count numbers above, below, or within a limit

Comparison criteria need quotation marks around the operator and number. For example, greater than 100 is written as ">100". On the B2:B9 sample values above, two cells are greater than 100: 120 and 150.

With the sample values in B2:B9, count values greater than 100 with =COUNTIF(B2:B9,">100"). The result is 2.

Common operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). For instance, the sample range has four values greater than or equal to 100: 100, 120, 150, and 100.

With values in B2:B9, count values of 100 or more with =COUNTIF(B2:B9,">=100"). The result is 4.

When the threshold is stored in a cell, join the operator to the cell reference with the ampersand. If D1 contains 100, Excel builds the criterion from the text operator and the value in D1. This means you can change the limit in D1 and keep the formula unchanged.

With values in B2:B9 and a threshold in D1, count values above that threshold with =COUNTIF(B2:B9,">"&D1). If D1 is 100, the result is 2.

COUNTIF accepts one condition. For a range of values, subtract one count from another, or use COUNTIFS when the task is to count records meeting multiple conditions. For example, COUNTIFS can combine a numeric threshold in a sales column with a matching region in another column.

Count cells containing a digit in text

A numeric value is not the same as text that includes a digit. If a column contains product labels such as A7-Blue or REF17, and you want every cell whose text includes the character 7, use wildcard asterisks around the digit. The asterisk represents any number of characters, including none.

With labels in A2:A20, count cells whose text contains the digit 7 with =COUNTIF(A2:A20,"*7*"). This can match labels such as A7-Blue and REF17, as well as text consisting only of 7.

This is a text search, not a way to find numeric values greater than or equal to 7. Wildcards are for matching text; use a comparison criterion for arithmetic values. Also, a wildcard search for 7 will match 17 and 70 because both contain that character. If you mean a whole word or a specific code structure, a simple wildcard may be too broad.

Excel also uses the question mark wildcard for one character and the tilde to escape wildcard characters. For example, a tilde before an asterisk means a literal asterisk rather than “any characters.” This matters when a column contains filenames or codes that actually include symbols such as * or ?.

Count all numeric cells: COUNT or COUNTIF?

If your goal is to count cells containing numbers, use COUNT rather than COUNTIF. COUNT includes numeric values and dates or times, because Excel stores dates and times as numbers. It also includes results of formulas that return numbers. It does not count empty cells or numbers stored as text.

With data in B2:B9 as listed earlier, count all numeric cells with =COUNT(B2:B9). The result is 7: the range has seven numeric entries and one blank.

COUNTIF is appropriate when you are counting numbers that meet a condition, such as values equal to 25 or above 100. It is not a dedicated test for “is this cell numeric?” A criterion like greater than zero would miss zero and negative values, so it cannot reliably count every numeric entry. For an unconditional numeric count, COUNT is simpler and expresses the intent directly.

If you need to count numeric cells only in part of a range that meets another condition, COUNTIFS may fit when the conditions are represented by ordinary criteria. For more complex tests, functions such as SUMPRODUCT with ISNUMBER can be useful, but they are less straightforward to maintain. Choose the simplest formula that captures the actual rule.

Numbers stored as text and other surprises

A cell can display 25 while storing the text string “25.” This can happen after importing data, copying values from a website, or entering a value with a leading apostrophe. The green triangle Excel may show in a cell can indicate a number stored as text, although indicators depend on Excel’s error-checking settings.

COUNT will not include a number stored as text. COUNTIF criteria can sometimes appear to match numeric-looking text as well as numeric values, depending on the criterion and the data. If the distinction matters, do not rely on display alone: inspect the cell, test the data type, and try the formula on a small sample with known values. Convert the text to real numbers if the column is intended for arithmetic.

In Excel for Microsoft 365 on Windows, you can often select affected cells and use the warning menu to convert text to numbers. Another option is Data > Text to Columns, then finish the wizard without changing the intended delimiter settings. A multiplication-by-one helper calculation can also coerce suitable numeric text, but verify the result before replacing source data. Keep a copy of imported data when conversion may remove meaningful formatting or leading zeros.

Leading zeros are a reason not to convert automatically. An identifier such as 00731 may be a code, not the number 731; converting it to a number removes the zeros. Decide whether the column represents quantities for calculation or labels for identification before changing its type.

Common formula errors and how to fix them

  • Missing quotation marks around an operator: Write a comparison criterion as ">100", not as an unquoted operator-and-number argument.
  • Putting a cell reference inside quotes: If D1 holds the target, use D1 for an exact match, or join it to a comparison operator with &. Quoting D1 makes it literal text.
  • Counting the wrong range: Check that the range starts and ends on the intended rows. A formula can be syntactically valid and still omit new data below its last row.
  • Expecting formatting to change the count: A number format changes how a value appears, not the value COUNTIF evaluates. Check the underlying value or adjust the criterion to the actual rule.
  • Using the wrong separator: The formulas here use commas, the standard list separator in many Excel installations. Some regional settings use semicolons; if Excel rejects a formula with commas, check the list separator configured for your locale.
  • Expecting COUNTIF to handle several conditions: Use COUNTIFS for multiple conditions, such as a date range and a department, or combine separate counts only when that matches the logic you need.

When a result seems wrong, test the formula against a short range where you can inspect every value. Check for blanks, hidden spaces in imported text, numbers stored as text, and criteria that are too broad or too narrow. If cells contain formulas, inspect their returned values rather than assuming the formula text itself is what COUNTIF counts.

When COUNTIFS or another function is a better fit

Use COUNTIFS when every counted row must meet two or more conditions. For example, you might count orders where the amount is at least 100 and the region is North. Each criteria range must correspond to the same rows as the others. This makes COUNTIFS useful for row-based questions, while COUNTIF is suited to a single rule applied to one range.

If you are counting every nonblank cell, COUNTA may be more appropriate than COUNT or COUNTIF. COUNTA counts cells containing any value, including text, numbers, and some formula results; it does not mean “count numbers.” If you need the number of unique numeric values rather than the number of numeric cells, that is a different task again, because repeated values should be counted only once.

For a quick visual count, a filter can help you inspect matching records, but it is not a substitute for a formula when you need a result that updates with data. A formula is also easier to reuse in a summary cell or dashboard. Keep the goal precise: count matches, count numeric entries, count distinct values, and find text containing digits are four different operations.

Frequently Asked Questions

Can COUNTIF count an exact number?
Yes. Use a numeric criterion such as =COUNTIF(B2:B20,25) to count cells equal to 25. Replace the range and number as needed.

How do I count numbers greater than a value in another cell?
Join the greater-than operator to the reference. With values in B2:B20 and the limit in D1, use =COUNTIF(B2:B20,">"&D1).

How do I count cells that contain any number?
Use COUNT, for example =COUNT(B2:B20). It counts numeric values, including dates and times, but not numeric-looking text.

Can COUNTIF find a digit inside a longer text value?
Yes. With text in A2:A20, =COUNTIF(A2:A20,"*7*") counts text entries containing 7 anywhere. It also matches values such as 17, so use it only when that broad match is intended.

Why does my count ignore a cell that looks like a number?
The value may be stored as text, or the displayed value may be rounded while the underlying value differs. Check the cell’s stored type and full value, then convert or adjust the criterion if appropriate.

Related

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.