COUNTIF Values in Excel: Count Text, Numbers, and More

Formulas & functions · Updated November 22, 2026

In Excel, use =COUNTIF(range,criteria) to count cells in a range that meet one condition. For example, with order statuses in A2:A20, =COUNTIF(A2:A20,"Paid") counts the cells whose value is Paid, without distinguishing uppercase from lowercase.

What COUNTIF counts

COUNTIF is a worksheet function for counting cells that match a single test. The test can be a particular word, number, date, comparison, wildcard pattern, or a value stored in another cell. It returns a number, not the matching cells themselves, so it is useful for summaries such as how many orders are pending or how many scores meet a threshold.

The function examines the cells in the range you specify and counts each matching cell once. It does not count how many times a word appears inside one cell: a cell containing “Paid, shipped” is one cell, and an exact criterion of Paid will not match that whole cell. COUNTIF is also not a case-sensitive search. A criterion of North matches North, NORTH, and north.

COUNTIF does not count by formatting. A cell’s fill color, font color, or bold formatting is not a criterion that COUNTIF can evaluate. If the distinction matters, use a helper column that records the relevant category as data, or consider a filter or other appropriate approach.

Understand the syntax and arguments

The function has two arguments: the cells to inspect and the condition each cell must meet. The range is usually a cell reference, and the criteria can be typed into the formula or supplied by a cell reference.

  • range: The cells Excel checks, such as A2:A20.
  • criteria: The condition, such as a word, a number, or a comparison operator followed by a value.

With product names in A2:A10 and the word Tea appearing in four cells, =COUNTIF(A2:A10,"Tea") returns 4. Text criteria go inside straight double quotation marks when typed directly into a formula. If a criterion is a cell reference, the reference itself is not quoted.

On Microsoft 365 for Windows, enter a formula in an empty cell and press Enter. You can also select the Formulas tab and choose More Functions > Statistical > COUNTIF to open the Function Arguments dialog. Excel for Mac has the same function and formula syntax; ribbon organization can vary somewhat by release. In Excel for the web, enter the formula in a cell as usual. Some regional settings use semicolons rather than commas between arguments, so Excel may adjust the separator to match your configuration.

Count exact text, numbers, and values in cells

For a specific text value, put the text in quotation marks. For a specific numeric value, enter the number as the criterion without quotation marks. Both approaches compare each cell in the selected range against the requested value.

With department names in B2:B30, suppose six cells contain Sales. The formula =COUNTIF(B2:B30,"Sales") returns 6. It matches a whole cell value, not just a portion of a longer phrase; use a wildcard if you want to match text that contains Sales along with other characters.

With scores in C2:C21, suppose 82 occurs three times. The formula =COUNTIF(C2:C21,82) returns 3. A number stored as text may behave differently from a true numeric value, particularly when data has been imported from another system. If a count seems unexpectedly low, check whether the cells contain numbers or text that only looks like numbers. Converting the source data to a consistent type is usually more dependable than trying to work around mixed data in the criterion.

You can also put the criterion in a worksheet cell so that changing the cell changes the count. With categories in A2:A50 and the category to count in D2, =COUNTIF(A2:A50,D2) returns 8 if D2 contains Hardware and eight cells in the range match. This is convenient for a small report where a reader can select or type a category without editing the formula.

Use comparison operators and date criteria

For thresholds, place an operator and its value in the criteria. COUNTIF recognizes operators such as greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). When the operator and value are written together as text, enclose the criterion in quotation marks.

With test scores in C2:C21, suppose seven scores are at least 80. The formula =COUNTIF(C2:C21,">=80") returns 7. To make the cutoff adjustable, join the operator to a cell reference with the ampersand. With the same scores in C2:C21 and a cutoff in E1, suppose E1 contains 80 and seven scores meet it. The formula =COUNTIF(C2:C21,">="&E1) returns 7.

Excel stores valid dates as serial numbers, which means date comparisons can use the same operators as numeric comparisons. Use DATE to construct a reliable date criterion rather than typing a date string whose interpretation might depend on regional settings. With shipment dates in D2:D100, suppose six dates fall on or after January 1, 2026. The formula =COUNTIF(D2:D100,">="&DATE(2026,1,1)) returns 6. The source cells must contain real Excel dates for this comparison to work as intended; text that merely looks like a date may not compare correctly.

Count partial text with wildcards

COUNTIF supports two wildcards in text criteria. An asterisk (*) stands for any number of characters, including none. A question mark (?) stands for exactly one character. These let you count values without needing every cell to contain the same complete text.

  • With item descriptions in A2:A40, =COUNTIF(A2:A40,"*apple*") counts cells containing apple anywhere in the text. If there are five such descriptions, the result is 5.
  • With codes in B2:B40, =COUNTIF(B2:B40,"AB?7") matches a four-character value beginning AB, followed by any single character, and ending in 7. If three cells match, the result is 3.
  • With email addresses in C2:C40, =COUNTIF(C2:C40,"*@example.com") counts addresses ending in @example.com. If nine cells match, the result is 9.

If the text you want to find includes a literal asterisk or question mark, precede that character with a tilde (~) in the criterion. For instance, a tilde tells COUNTIF to treat the next wildcard character as an ordinary character. Wildcards apply to text matching; they are not a way to perform arbitrary regular-expression searches.

Count blanks, nonblank cells, and alternatives

Blank-related counts can answer practical questions such as how many records still need an owner. With assigned names in D2:D50, suppose five cells are empty. The formula =COUNTIF(D2:D50,"") returns 5. A blank criterion can also count cells whose formulas return an empty string, so it may count more than cells that were never filled in. If that distinction matters, inspect how the source cells were created or use a method tailored to the data.

To count cells that are not blank, use a not-equal-to-empty criterion. With the same names in D2:D50, suppose 44 cells contain a name and one is empty. The formula =COUNTIF(D2:D50,"<>") returns 44 for that example. Be aware that cells containing spaces are not truly empty; a space can make a cell count as nonblank even though it looks blank on screen.

COUNTIF handles one condition per function call. To count cells that meet two or more conditions across ranges, use COUNTIFS instead. For example, a report might need orders that are both in a particular region and have a particular status. To count either of two categories in one range, you can add two COUNTIF results; make sure the categories do not overlap in a way that would count the same cell twice. With statuses in A2:A30, suppose four cells say Open and three say Pending, with no other matching overlap. The formula =COUNTIF(A2:A30,"Open")+COUNTIF(A2:A30,"Pending") returns 7.

Choose between COUNTIF and related functions

COUNTIF is a good fit when the question has one condition and the answer needed is a count. Other functions are more suitable when the question changes:

  • COUNTIFS: Use it when all of several conditions must be true, such as a matching region and a matching status. Each condition range should correspond to the same rows and have the same dimensions.
  • COUNT: Use it to count cells containing numbers, rather than to match a particular number or condition.
  • COUNTA: Use it to count cells that are not empty, including cells containing text. A formula that returns an empty string is still a formula in its cell, which can affect how nonempty counts behave.
  • COUNTBLANK: Use it when the task is specifically to count blank cells, including cells with formulas that return an empty string.
  • Filters or PivotTables: Use these when you need to inspect, group, or summarize many categories rather than calculate one count with one criterion.

These functions answer related but not identical questions. For example, counting every numeric cell is different from counting cells equal to zero, and counting cells that are not empty is different from counting cells that contain a visible, non-space value. Choose the function based on what you mean by “count,” not only on what the cells look like.

Common COUNTIF problems and fixes

If a result is zero when you expect a match, first check for leading or trailing spaces, inconsistent spelling, and values stored as text rather than numbers. A cell that appears to say Paid might contain an extra space. A criterion can also fail if the source has a different character, such as a nonbreaking space copied from a web page. Cleaning or standardizing the source data often resolves the issue.

Check that the range includes the rows you intend to count and does not accidentally include a heading. If you copy a formula down a column and the range shifts, make it absolute with dollar signs. With statuses in A2:A100, copying the formula down while keeping that range fixed, and a category in D2, the formula =COUNTIF($A$2:$A$100,D2) returns 12 if the category in D2 occurs 12 times. The dollar signs lock the range when the formula is copied; they do not lock D2.

Another common mistake is putting a comparison operator in the wrong place. A criterion such as greater than 10 must be supplied as one criteria expression, not as a separate extra argument. When the cutoff comes from a cell, join the quoted operator to the reference with an ampersand. Also remember that COUNTIF is case-insensitive and does not count by cell color. If you need case-sensitive comparisons or formatting-based counts, COUNTIF alone is not the right tool.

Frequently Asked Questions

How do I count cells that contain a specific word in Excel?
Use COUNTIF with the range and the word as the criterion. For an exact cell match, write the word in quotation marks. To count cells where the word appears anywhere in longer text, surround it with asterisks in the criterion.

How do I count values greater than a number?
Use a greater-than criterion such as ">10". If the limit is stored in a cell, join a quoted operator such as ">" to that cell reference with an ampersand. The cells being checked should contain real numbers.

Can COUNTIF count multiple conditions?
One COUNTIF call accepts one condition. Use COUNTIFS when multiple conditions must all be met, or combine COUNTIF results when you want to count alternatives in the same range. Consider whether alternative conditions could overlap and count some cells twice.

Why does COUNTIF return zero for a value that looks like a match?
Likely causes include extra spaces, different spelling, a number stored as text, or a range that omits the cell. Check the underlying cell contents and data type, not just the displayed appearance. COUNTIF also treats text matching as case-insensitive, so capitalization alone is not the cause.

Can COUNTIF count cells by color?
No. COUNTIF evaluates cell values against a criterion, not fill or font colors. If color represents a category, record that category in a cell and count the recorded value, or use a different method designed for formatting-based analysis.

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.