Excel COUNTIF Criteria: Rules, Examples, and Fixes

Formulas & functions · Updated November 18, 2026

In Excel, COUNTIF criteria are the conditions that tell the COUNTIF function which cells to count. The syntax is =COUNTIF(range,criteria): give it a range and one criterion, such as "Paid", ">100", or a reference to a cell containing the condition.

How COUNTIF criteria work

COUNTIF checks each cell in a range against one condition and returns the number of cells that match. The range can contain text, numbers, dates, or blanks; the criterion determines what qualifies. In Microsoft Excel for Microsoft 365 on Windows, enter the formula in a cell and press Enter. The function recalculates when matching values change.

For example, with department names in A2:A8 and East appearing three times, the formula =COUNTIF(A2:A8,"East") returns 3. The criterion is written in quotation marks because it is text. In a formula, the first argument is always the cells to inspect and the second is the condition to apply.

COUNTIF accepts one condition. It does not add up the values in matching cells; it only counts them. To total matching values, use a summing function such as SUMIF. To count rows that meet more than one condition, use COUNTIFS, covered below.

Exact text and number criteria

For a text match, put the text in double quotation marks inside the formula. COUNTIF is not case-sensitive, so "east" and "East" match the same entries. A criterion such as "East" looks for the cell value East, rather than a longer entry such as Northeast.

With department names in A2:A8 containing East, West, East, South, West, East, and North, this formula returns 3: =COUNTIF(A2:A8,"East"). Spaces are significant: a cell containing East with a trailing space may not match "East". If an apparently identical value is missed, check for extra spaces or other hidden characters.

For a numeric criterion, you can use a number directly or put a comparison operator and number in quotation marks. Suppose order totals in B2:B8 are 125, 80, 125, 240, 65, 125, and 90. With those values in B2:B8, =COUNTIF(B2:B8,">100") returns 4: three values of 125 and one value of 240 are greater than 100. The test is strictly greater than; to include 100, use ">=100".

Common comparison operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to). For a simple equal-to test, the operator is optional: the criterion 100 counts values equal to 100.

Wildcards for partial text matches

COUNTIF supports wildcards when the criterion is text. An asterisk (*) represents any sequence of characters, including no characters; a question mark (?) represents exactly one character. This makes it possible to count entries that start with, end with, or contain particular text.

  • "Jo*" matches text beginning with Jo, including Jo, Joan, and John.
  • "*son" matches text ending in son.
  • "An?" matches three-character entries beginning with An, such as Ana or Ann.
  • "*east*" matches text containing east anywhere in the cell.

With names in A2:A7 containing Ana, Ann, Anne, Jo, Joan, and John, =COUNTIF(A2:A7,"Jo*") returns 3: Jo, Joan, and John match. The formula =COUNTIF(A2:A7,"An?") returns 2, matching Ana and Ann but not the four-character name Anne.

To search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde. For instance, the criterion "~*" means a literal asterisk. A tilde itself is represented by two tildes, "~~". This escape character is useful when counting values that contain symbols used in a spreadsheet or product code.

Using a cell reference as the criterion

A criterion does not have to be typed directly into the formula. Point to a cell instead, which is helpful when someone needs to change the condition without editing the formula. If statuses are in C2:C8 and E2 contains Paid, =COUNTIF(C2:C8,E2) counts entries equal to the value in E2. With Paid appearing four times in C2:C8, the result is 4.

When a comparison operator needs to be combined with a cell reference, join the operator text to the reference with an ampersand. With scores in B2:B8 and a threshold in E2 containing 100, =COUNTIF(B2:B8,">"&E2) counts values greater than the threshold. If the scores are 125, 80, 125, 240, 65, 125, and 90, the result is 4. Without the ampersand, Excel would not combine the operator and the value in E2 into one criterion.

Keep the range and criterion reference suitable for how the formula will be copied. For example, if copying a formula down a column while keeping the data range fixed, an absolute range such as $C$2:$C$8 prevents that range from shifting. The dollar signs do not change what COUNTIF counts; they control how cell references adjust when a formula is copied.

Criteria for dates

Excel stores dates as serial numbers, so COUNTIF can compare dates using equality or comparison operators. The key is to make the criterion a real date value, not text that only looks like a date. Date interpretation can depend on regional settings, so formulas that construct a date with DATE are often clearer than typing a date string into the condition.

Suppose actual Excel dates in D2:D5 are March 1, March 15, March 31, and April 1, 2026. With those dates in D2:D5, =COUNTIF(D2:D5,DATE(2026,3,15)) returns 1 because there is one March 15 date. COUNTIF can also test a date boundary, but it still accepts just one condition.

To count every date in March, including March 1 and March 31 but excluding April 1, use two conditions with COUNTIFS. With the same dates in D2:D5, =COUNTIFS(D2:D5,">="&DATE(2026,3,1),D2:D5,"<"&DATE(2026,4,1)) returns 3. The inclusive lower bound and exclusive first day of the next month avoid missing dates that include a time as well as a date.

Counting blanks and nonblank cells

Use an empty-text criterion to count blank cells. With entries in A2:A10, =COUNTIF(A2:A10,"") counts cells that Excel treats as empty. This is useful for finding incomplete fields, but note that a formula returning an empty string can also behave like a blank for this criterion. A cell that appears empty may therefore not be truly empty.

To count cells that are not blank, use the not-equal operator with an empty string. With the same range A2:A10, =COUNTIF(A2:A10,"<>") counts nonblank entries. If the result seems surprising, inspect cells that contain formulas, spaces, or invisible characters. A space is content, even if it is hard to see, so it is not the same as an empty cell.

COUNTIF versus COUNTIFS

Choose COUNTIF when one condition is enough, such as counting one status or values above a threshold. Choose COUNTIFS when a record must satisfy multiple conditions at once. COUNTIFS takes pairs of ranges and criteria, and a row must meet every supplied condition to be counted.

For example, suppose regions are in A2:A10 and order statuses are in B2:B10. To count rows where the region is East and the status is Paid, use =COUNTIFS(A2:A10,"East",B2:B10,"Paid"). The result is the number of rows matching both conditions. Keep the criteria ranges the same size and shape so each row’s region is evaluated alongside that row’s status.

COUNTIFS is not the same as counting either condition. Its criteria are combined as AND tests: a row must meet all of them. If the task is to count East or West, use separate COUNTIF results and add them, or structure the data and formula for the specific requirement. Avoid assuming that adding criteria to COUNTIFS creates an OR condition.

Common COUNTIF problems and fixes

If a formula returns zero when matches appear visible, check the data and criterion before changing the function. Text and numbers that look alike may be stored differently: a number stored as text may not behave like a numeric value in a comparison. Also check for leading or trailing spaces, inconsistent spelling, and cells that contain formulas returning text.

  • Confirm the range includes the expected rows and does not omit the final entry.
  • Check that the criterion uses quotation marks for text and comparison expressions such as ">100".
  • For a cell-based comparison, join the operator and reference with &.
  • Use wildcards only when a partial match is intended; * and ? have special meanings.
  • For dates, verify that the cells contain real Excel dates rather than text-formatted date strings.

In English-language Excel, formulas commonly use commas between arguments, as in the examples here. Depending on Windows regional settings, Excel may display or require semicolons instead. Excel for Mac uses the same COUNTIF function and criterion logic, although regional settings can likewise affect the argument separator. If a formula is rejected, check the separator expected by that installation.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase? No. COUNTIF is case-insensitive, so a criterion of East also matches EAST or east. If capitalization must distinguish values, COUNTIF alone does not provide a case-sensitive match.

How do I count cells that contain a word anywhere? Use an asterisk on both sides of the word in the criterion. For example, with text in A2:A20, =COUNTIF(A2:A20,"*apple*") counts cells containing apple anywhere in their text, regardless of capitalization.

Can COUNTIF use two criteria? No. COUNTIF takes one condition. Use COUNTIFS when a row must satisfy two or more conditions, such as a matching region and status.

Why does COUNTIF return zero for a date? The visible date may be stored as text, or the criterion may not be a date value in the same way Excel stores dates. Check the cell values and use a real date or a DATE-based criterion where appropriate.

Can COUNTIF count cells that are not equal to a value? Yes. Use the not-equal operator, such as "<>Paid" to count entries other than Paid. If you also need to exclude blanks, verify how blank cells and formula results are represented in the range.

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.