Excel COUNTIF Equal to a Number: Formula and Examples

Formulas & functions · Updated November 19, 2026

To count cells equal to a number in Excel, use =COUNTIF(B2:B10,100), replacing the range and number with your own. COUNTIF counts cells in the chosen range that meet a criterion, so this formula returns how many cells in B2:B10 contain 100.

COUNTIF syntax and the equality test

COUNTIF has two arguments: the range to examine and the criterion to match. In Microsoft Excel for Microsoft 365 on Windows, its syntax is =COUNTIF(range,criteria). The function treats a number used as the criterion as an equality test. You do not need to type an equals sign before a plain numeric criterion.

For example, with scores in B2:B10, =COUNTIF(B2:B10,100) counts cells whose value is 100. It does not count values such as 99 or 101. The result is one number, which can be displayed in any suitable number format. COUNTIF is available in current desktop Excel, Excel for Mac, and Excel for the web; the formula syntax is the same in each.

To enter it, select the cell where you want the count, type the formula, and press Enter. Excel generally colors or outlines referenced cells while you edit the formula, which can help you check that the range is correct. If the data grows, consider using a larger range or an Excel table so newly added records are included in your count.

Count an exact number in a range

Use a number directly as the second argument when the target value is fixed. In a list of product quantities, for instance, you might want to know how many orders contain exactly five items. The following examples show the range and criterion together; the result depends on the values in the cells.

  • With quantities in C2:C50, =COUNTIF(C2:C50,5) counts cells equal to 5.
  • With test scores in D2:D101, =COUNTIF(D2:D101,0) counts zero scores, not blank cells.
  • With invoice totals in E2:E200, =COUNTIF(E2:E200,250) counts totals equal to 250, subject to the underlying stored values.

Those formulas count matches in a single range and return a count, not the matching values themselves. A cell containing 5.00 is numerically equal to 5, even if its number format shows two decimal places. A cell showing “5” because it is formatted with no decimal places might instead contain 5.4; in that case its stored value is not equal to 5. What appears on screen is not always the value Excel compares.

Use an explicit equals criterion or a cell reference

You can also write equality explicitly by putting an equals sign inside quotation marks. With quantities in C2:C50, =COUNTIF(C2:C50,"=5") counts cells equal to 5. This is equivalent to using the numeric criterion 5 for this purpose. The explicit form can be helpful when you are learning how COUNTIF criteria work, but it is not required for a simple number match.

If the number to match is stored in another cell, refer to that cell rather than editing the formula each time. With quantities in C2:C50 and the target number in F2, =COUNTIF(C2:C50,F2) counts occurrences of the value in F2. Changing F2 updates the result automatically. This makes a small summary area convenient: you can list several target numbers in one column and copy the count formula down next to them, adjusting the target-cell reference as needed.

To keep the target fixed while copying a formula, use an absolute cell reference. With values in C2:C50 and the target in F2, =COUNTIF(C2:C50,$F$2) keeps F2 as the target if you copy the formula to another row or column. The dollar signs lock the reference; they do not change what COUNTIF counts.

Count values greater than, less than, or within a range

COUNTIF can count numbers that meet a comparison rather than only an exact match. For criteria that use operators such as greater than or less than, put the operator and number together inside quotation marks. With scores in B2:B20, =COUNTIF(B2:B20,">80") counts scores greater than 80; a score of exactly 80 is excluded.

Common numeric criteria include:

  • With amounts in B2:B100, =COUNTIF(B2:B100,">=100") counts values at least 100.
  • With temperatures in C2:C40, =COUNTIF(C2:C40,"<0") counts values below zero.
  • With ratings in D2:D80, =COUNTIF(D2:D80,"<>3") counts values not equal to 3, while also potentially counting other nonblank contents that meet the criterion.

For a threshold held in a cell, join the operator to the cell reference with the ampersand. With scores in B2:B20 and the threshold in F2, =COUNTIF(B2:B20,">"&F2) counts values greater than F2. This construction is useful when a report has an adjustable cutoff. To count a value between two limits, combine two COUNTIF results by subtraction only if the logic is appropriate, or use COUNTIFS to apply both limits to the same range. COUNTIFS is designed for multiple criteria; it is not necessary for a straightforward equality count.

Numbers stored as text and other mismatches

A frequent source of surprising results is data that looks numeric but is stored as text. This can happen after copying data from a website, importing a text file, or entering a number with a leading apostrophe. A cell may display 100 while its stored content is the text string “100,” rather than the number 100. Criteria matching can be affected by how the data is represented, so do not assume that identical-looking cells have identical underlying types.

When a count seems wrong, inspect a few source cells. Select a cell and look at the formula bar; an apostrophe may indicate text entry. Excel may also show a green error indicator for a number stored as text, depending on its error-checking settings. The Number format on the Home tab can change how a value looks, but changing the format alone does not reliably convert text into a number.

For data you are allowed to clean, select the affected cells and use the warning menu’s Convert to Number option when Excel offers it. Another option is Data > Text to Columns, then finish the wizard with the appropriate settings. The best method depends on the source and workbook, so check a few converted values before relying on the count. If preserving the original data matters, make a copy first. Avoid coercing values blindly: identifiers such as postal codes can look numeric but should remain text because their digits are labels, not quantities.

Decimals, displayed precision, and dates

COUNTIF tests stored values, not the rounded appearance created by a cell’s number format. If a calculation produces 9.999999 and the cell displays 10, an exact count for 10 may not include it. This can occur because decimal fractions are not always represented exactly in binary arithmetic. If the task is to count values rounded to a chosen number of decimal places, make that rule explicit rather than relying on display formatting. For example, you can create a helper column with rounded values and count the helper results, after confirming that rounding matches the intended business rule.

Dates in Excel are stored as serial numbers and displayed using date formats. For a date equality test, use a real date value as the criterion rather than relying on a text string whose interpretation may vary by regional settings. With dates in A2:A100, =COUNTIF(A2:A100,DATE(2026,1,15)) counts entries equal to January 15, 2026. It will not count date-time values that include a non-midnight time fraction, even if the cells are formatted to show only the date. For a whole calendar day that may include times, use a pair of boundary criteria with COUNTIFS.

Common errors and how to diagnose them

If COUNTIF returns zero or a number you did not expect, check these points before changing the formula:

  1. Confirm that the range includes the cells containing the data, and not a nearby heading or summary cell.
  2. Check that the target is the intended numeric value. A value displayed as 100 may contain decimals that are hidden by formatting.
  3. Look for numbers stored as text, extra spaces in imported data, or other inconsistencies in the source.
  4. Make sure comparison operators are inside quotation marks when they are part of a criterion, and join a cell reference to an operator with &.
  5. Check whether the formula result is stale because calculation is set to Manual. In Excel, review Formulas > Calculation Options.

Also note that COUNTIF is not case-sensitive for text criteria, but that is usually irrelevant to a numeric match. A number format such as currency or percentage changes the display, not the underlying comparison: a cell containing the numeric value 100 can be displayed as $100, while a percentage-formatted cell containing 1 displays as 100%. Compare the actual stored values and the units used in the data, not just the visible symbols.

COUNTIF compared with COUNT and COUNTIFS

COUNT and COUNTIF solve related but different problems. COUNT counts cells containing numbers, regardless of whether their values equal a particular target. COUNTIF counts cells that satisfy one condition, such as being equal to 100. Use COUNT when you need the number of numeric entries in a range; use COUNTIF when you need the number of entries matching a value or rule.

COUNTIFS is useful when a record must meet multiple conditions. For example, a report might need to count rows for one department where the amount also equals a specified number. Because COUNTIFS applies conditions together, it can express that kind of multi-condition test more directly than a single COUNTIF. For just one range and one numeric equality criterion, COUNTIF is shorter and easier to read.

If you need to return the matching rows rather than count them, a counting function is not the right tool. Excel 2021 and Microsoft 365 include FILTER, which can return records that meet criteria. If you only need the number of exact matches, COUNTIF is generally the more direct choice.

Frequently Asked Questions

Do I need to type an equals sign in COUNTIF?

No. A plain numeric criterion such as 100 means equal to 100. You may write the criterion as “=100” to make equality explicit, but the equals sign is optional for a simple number match.

Does COUNTIF count a number shown with decimals?

It counts according to the stored value, not merely the displayed format. A stored value of 5 formatted to show 5.00 equals 5, but a stored value of 5.01 does not, even if formatting hides the decimal places.

How do I count a number listed in another cell?

Use the target cell as COUNTIF’s second argument. For example, with the data range in C2:C50 and the target in F2, =COUNTIF(C2:C50,F2) counts values matching F2. Lock F2 with dollar signs if you need the reference to stay fixed when copying the formula.

Why does COUNTIF return zero when the numbers look the same?

The cells may contain different underlying values or data types. Check for hidden decimal places, numbers stored as text, imported spaces, and date-time fractions. Also verify that the formula’s range covers the data you mean to count.

Can COUNTIF count dates equal to a specific date?

Yes. Excel dates are numeric serial values, so COUNTIF can match a date criterion. If source cells contain times as well as dates, an exact date match may miss them; use a start-of-day and end-of-day condition with COUNTIFS when you mean every time on that calendar date.

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.