COUNTIF Criteria Examples for Excel

In Excel for Microsoft 365, COUNTIF counts cells in one range that meet one condition: enter the range and a criterion such as exact text, a number comparison, a wildcard, or a cell reference. For example, with regions in A2:A12, =COUNTIF(A2:A12,"North") returns 4 when four cells contain North.

How COUNTIF criteria work

COUNTIF has two arguments: the range to examine and the criterion to test. The criterion can be text, a number, an expression written as text, a wildcard pattern, or a reference to a cell containing a criterion. Excel checks each cell in the range and counts those that match; it does not add the matching values together.

For the examples below, assume A2:A12 contains North, South, North, West, East, North, South, West, North, East, and one genuinely empty cell. Assume B2:B12 contains 120, 85, 150, 0, 210, 95, 175, 60, 130, 200, and 100. The examples state their own layouts where they use other ranges. These sample values make it possible to check the returned counts rather than treating the formulas as abstract templates.

In Excel, enter a formula in a cell and press Enter. The formula bar and worksheet use the same formula syntax. In the examples, commas separate arguments, as they do in the standard English-language Windows installation; some regional settings use semicolons instead.

Exact text criteria

To count cells equal to a particular text value, put the text in straight double quotation marks inside the formula. With the region names in A2:A12 as listed above, the formula =COUNTIF(A2:A12,"North") returns 4. The match is for the cell value, not for a phrase that merely contains the word North.

Text matching in COUNTIF is not case-sensitive. The criterion North also matches NORTH or north. COUNTIF does not provide a switch for case-sensitive matching; if capitalization itself matters, use a different approach, such as a formula based on EXACT, rather than assuming COUNTIF distinguishes letter case.

Spaces are part of text values. If a cell contains a leading or trailing space, an exact criterion may not match the value you intended. A useful cleanup step is to inspect the source data or use Excel’s TRIM function in a helper column. Do not add spaces around the criterion text unless those spaces are genuinely part of the value you want to count.

Numbers and comparison operators

A criterion can be a number or a comparison such as greater than, less than, equal to, or not equal to. When an operator is combined with a number, place the whole criterion in quotation marks. With the sample numbers in B2:B12, =COUNTIF(B2:B12,">100") returns 6: the matching values are 120, 150, 210, 175, 130, and 200.

For a threshold that includes the boundary, use the equals sign with the comparison operator. With the same B2:B12 values, =COUNTIF(B2:B12,"<=100") returns 5, counting 85, 0, 95, 60, and 100. This distinction matters in grading bands, stock limits, and similar checks: greater than 100 does not include 100, while greater than or equal to 100 does.

For a criterion that is simply equal to a number, you can use the number without quotation marks. With the sample data in B2:B12, =COUNTIF(B2:B12,100) returns 1. The same criterion can be written as the text “=100”, but the direct numeric form is usually easier to read when there is no comparison operator.

Criteria stored in another cell

Using a cell reference makes a count easier to adjust without editing the formula. For an exact text match, put North in D2 and use the region values in A2:A12: =COUNTIF(A2:A12,D2) returns 4. Changing D2 to South changes the result to 2.

To build a comparison from a cell, join the operator to the cell reference with the ampersand (&). Suppose D2 contains the number 100 and B2:B12 contains the sample numbers above. The formula =COUNTIF(B2:B12,">"&D2) returns 6. The ampersand combines the text operator with the value in D2 to form the criterion >100. If D2 changes to 150, the count updates to 3: 175, 200, and 210.

This approach is useful for interactive reports: put a product, status, or threshold in a clearly labeled input cell, then refer to it in the criterion. Keep the input cell’s data type appropriate. A date stored as text, for example, may not compare as expected against genuine Excel dates.

Wildcard criteria for partial text

COUNTIF supports two common wildcards in text criteria. An asterisk (*) matches any sequence of characters, including no characters; a question mark (?) matches exactly one character. These are helpful for counting names, codes, or descriptions when the full text varies.

Assume C2:C8 contains Avery, Avery Lane, Lane, Avery, Avery, Mia Avery, Mia, and Avery followed by a trailing space (seven cells total: Avery; Avery Lane; Lane, Avery; Avery; Mia Avery; Mia; and Avery with a trailing space). With those seven values in C2:C8, =COUNTIF(C2:C8,"Avery*") returns 4. It matches values beginning with Avery, including Avery Lane and the value with a trailing space.

With the same C2:C8 values, =COUNTIF(C2:C8,"*Avery*") returns 5. This pattern counts any cell containing Avery anywhere, including Lane, Avery and Mia Avery. A pattern such as *Avery* can also match longer words containing those letters, so review the data if partial matches could include unwanted values.

The question mark stands for one character, not one word or one optional character. With the same sample list, =COUNTIF(C2:C8,"Aver?") returns 2 because it matches the two cells containing exactly Avery. If you need to search for a literal asterisk or question mark in the cell text, precede that character with a tilde (~) in the criterion. The tilde is COUNTIF’s escape character for wildcard symbols.

Counting blank and nonblank cells

An empty-text criterion counts blank cells. With the region data in A2:A12, which has one genuinely empty cell, =COUNTIF(A2:A12,"") returns 1. This can help reveal missing entries in a column that is expected to be complete.

To count cells that contain something, use the not-equal operator with an empty criterion. With the same A2:A12 range, =COUNTIF(A2:A12,"<>") returns 10. Be clear about what “blank” means in the source: a cell that looks empty may contain a space or a formula that displays an empty string. Those cases can behave differently from a genuinely empty cell, so check the underlying values when a count seems surprising.

Dates as COUNTIF criteria

Excel stores genuine dates as serial numbers, so COUNTIF can compare them with the same operators used for numbers. It is usually safer to construct a date criterion with DATE(year,month,day) than to type a date as text, because date text can be interpreted differently under different regional settings.

Assume F2:F6 contains the genuine Excel dates January 3, January 15, February 1, January 31, and March 4, 2025. The formula =COUNTIF(F2:F6,">="&DATE(2025,2,1)) returns 2, counting February 1 and March 4. DATE creates a date value for the comparison, and the ampersand joins it to the greater-than-or-equal operator.

COUNTIF applies one condition at a time. To count dates inside a start-and-end range, such as all dates in January, you need two conditions. Excel’s COUNTIFS function is designed for multiple criteria; it can test the same date range against both a lower and an upper boundary. For month-based reports, another robust option is to use the first day of the month as the lower boundary and the first day of the next month as an exclusive upper boundary, so every date and time in the month is included.

OR conditions and the limits of COUNTIF

A single COUNTIF criterion represents one condition. If you want to count cells matching either of two exact values, add two COUNTIF results. With regions in A2:A12, =COUNTIF(A2:A12,"North")+COUNTIF(A2:A12,"South") returns 6: four North entries plus two South entries.

Adding counts works cleanly when the alternatives are mutually exclusive, as they are for exact region names in one cell. Be careful when criteria can overlap. For example, a count of cells containing a word plus a count of cells beginning with that word could count some cells twice. If you need several conditions to be true at the same time, use COUNTIFS rather than treating separate COUNTIF formulas as an AND test.

COUNTIF accepts one range and one criterion. It cannot directly test a status in one column and an amount in another as a single condition pair. COUNTIFS handles multiple range-and-criterion pairs, with all supplied criteria needing to be met. Choosing the right function first makes a worksheet easier to audit and avoids misleading totals.

Common errors and how to troubleshoot them

Leaving comparison operators outside the quotes: the operator and its fixed number belong together as a text criterion, such as ">100". When the threshold is in a cell, join the operator and reference with &.
Using the wrong quotation marks: use straight double quotes in formulas. Curly quotation marks copied from formatted text can prevent Excel from recognizing a criterion.
Expecting case-sensitive matching: COUNTIF treats upper- and lowercase text as equivalent. Use a case-sensitive method if capitalization is part of the requirement.
Forgetting wildcard behavior: * and ? are special in text criteria. Use ~* or ~? when you mean to search for the literal symbol rather than a pattern.
Comparing text dates with real dates: check that the source cells contain Excel date values, not text that merely looks like dates. DATE can create an unambiguous criterion value.
Missing spaces or extra characters: an exact text criterion can fail if the source contains hidden spaces or punctuation. Check representative cells and clean inconsistent data where appropriate.
Counting the wrong range: confirm that the range covers the intended rows and does not include a heading. COUNTIF counts matches in the range you provide, including any unintended cells inside it.

When a result looks wrong, test the criterion on a small, visible sample first. Verify the cell values, the range boundaries, and whether the rule should be exact, partial, or a numerical comparison. This is often quicker than changing several parts of a formula at once.

Frequently Asked Questions

Can COUNTIF use more than one condition?

No. COUNTIF takes one range and one criterion. Use COUNTIFS when multiple conditions must all be met, such as a department and a minimum sales amount. For an either/or count, you can add separate COUNTIF results when the criteria do not overlap.

Does COUNTIF distinguish uppercase and lowercase?

No. COUNTIF text matching is not case-sensitive, so North and NORTH match the same criterion. If the difference in capitalization matters, use a case-sensitive method based on EXACT rather than COUNTIF alone.

How do I count cells that contain a word anywhere?

Use an asterisk on both sides of the word in a text criterion. For example, *Avery* matches text with Avery at the beginning, middle, or end. Remember that it can also match a longer word containing the same letters.

Why does COUNTIF return zero for a date?

A frequent cause is that the cells contain text rather than genuine Excel dates, or that the criterion is being interpreted as text. Check the underlying data type and create the comparison date with DATE when appropriate. Also confirm that the dates fall within the selected range.

Can COUNTIF count cells that are not empty?

Yes. The criterion <> counts nonblank cells, while an empty-string criterion counts blank cells. If cells that appear empty give unexpected results, check for spaces or formulas that return an empty string.

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.