COUNTIF Data Analysis Examples in Excel

COUNTIF data analysis examples show how to count spreadsheet records that meet a rule, such as a region, a sales threshold, a date range, or a text pattern. In Excel for Microsoft 365, COUNTIF uses one range and one criterion, making it useful for quick summaries and data-quality checks.

What COUNTIF Does and When to Use It

COUNTIF returns the number of cells in a range that match a condition. Analysts use it to turn a column of detailed records into a simple count: how many orders are from the North, how many values exceed a target, or how many cells are empty. It counts matching cells; it does not add the values in them or return the matching records.

The function is a good fit when each count depends on one condition. For example, counting orders marked “Complete” is a one-condition task. Counting completed orders from the North requires two conditions, so COUNTIFS is usually a better choice. COUNTIF is available in current desktop Excel, Excel for Mac, and Excel for the web. The examples below use Excel’s standard comma-separated formula syntax; some regional settings display semicolons between arguments instead.

How COUNTIF Criteria Work

COUNTIF has two arguments: the range to inspect and the criterion to apply. The criterion can be a number, text, comparison, wildcard pattern, or a reference to a cell containing a criterion. Text matching is not case-sensitive, so “north” and “North” are treated as the same text.

For a basic category count, assume cells A2:A11 contain these regions in order: North, South, North, East, South, North, East, South, North, East. With those region names in A2:A11, this formula counts North records and returns 4:

=COUNTIF(A2:A11,"North")

Comparison criteria use an operator inside quotation marks. The operators include greater than, less than, equal to, and not equal to. If the comparison value is in a cell, join the operator to that reference with an ampersand. This distinction matters: a criterion such as greater than 10 is supplied as text, while the range itself can contain numeric values.

Example: Count Categories and Compare Groups

Category counts are a fast way to check a data breakdown before building a chart or pivot table. Using the region list above, count East records with this formula, which returns 3:

=COUNTIF(A2:A11,"East")

To compare several categories, put each category name in a small summary table and refer to its cell rather than typing a new criterion into every formula. If F2 contains North and the region names are still in A2:A11, the formula below returns 4. Copying it down beside other labels updates the criterion reference automatically:

=COUNTIF(A2:A11,F2)

This approach makes a summary easier to maintain when labels change. It also reduces typing errors, such as counting “Nroth” by mistake. However, COUNTIF does not create a list of categories for you; the labels in the summary table must already be present or entered separately.

Example: Count Values Above or Below a Threshold

Suppose B2:B11 contains unit values in this order: 12, 0, 25, 18, 7, 30, 12, 5, 22, 0. To count values greater than 10, with the numbers in B2:B11, use this formula. It returns 6: the matching values are 12, 25, 18, 30, 12, and 22.

=COUNTIF(B2:B11,">10")

For a threshold stored in F2, join the operator to the cell reference. If F2 contains 10 and B2:B11 has the values above, this formula also returns 6:

=COUNTIF(B2:B11,">"&F2)

Using a cell for the threshold is convenient for a dashboard: a reader can change the target without editing the formula. To count values equal to zero in B2:B11, use =COUNTIF(B2:B11,0); the result is 2. Be aware that numbers stored as text may not behave like genuine numeric values in comparisons. If results look wrong, check the source cells’ data types and clean or convert the data before relying on the count.

Example: Count Dates in a Time Period

Excel stores real dates as serial values, so date conditions work best when the source column contains actual Excel dates rather than text that only looks like a date. Assume C2:C11 contains these 2026 dates: January 5, January 18, February 2, February 14, March 1, March 12, January 31, February 28, March 20, and January 9.

To count dates on or after February 1, 2026, in C2:C11, use a date constructed with DATE. This returns 6 because six entries fall on or after that day:

=COUNTIF(C2:C11,">="&DATE(2026,2,1))

To count dates before February 1 in the same range, use this formula, which returns 4:

=COUNTIF(C2:C11,"<"&DATE(2026,2,1))

For a full month, use a start date and an exclusive start date for the following month. This avoids problems caused by dates that include times: a record at noon on the last day is still before midnight on the first day of the next month. For more complex reporting across start and end dates, COUNTIFS can express both boundaries directly.

Example: Find Text Patterns and Missing Values

COUNTIF supports wildcards for matching text patterns. An asterisk represents any number of characters, while a question mark represents exactly one character. A tilde can escape a wildcard when you need to search for a literal asterisk or question mark. For example, “Tea*” matches text beginning with Tea, and “*tea*” finds text containing tea anywhere.

Suppose E2:E11 contains Green tea, Black tea, Tea bags, Coffee, Herbal tea, Green tea, cocoa, Tea sampler, Coffee beans, and iced tea. With those product labels in E2:E11, the formula below returns 7 because seven cells contain “tea” somewhere in the text:

=COUNTIF(E2:E11,"*tea*")

Wildcards are useful for broad checks, but they can also create false matches. A search for “*tea*” intentionally matches phrases such as “Tea bags” as well as “Green tea”; it is not a count of one exact product. If the task is to count exact labels, use the full text as the criterion instead.

To check for missing data, suppose D2:D11 contains Complete, Pending, Complete, Complete, Pending, Complete, Pending, Complete, Pending, and one blank cell. With those values in D2:D11, this formula returns 1:

=COUNTIF(D2:D11,"")

To count nonblank cells in that same range, use the not-blank criterion below; it returns 9:

=COUNTIF(D2:D11,"<>")

A cell that looks empty may contain a space or a formula that returns an empty string, so a count of blanks may not identify every data-quality issue in the way you expect. Inspect the source when a blank count does not match what you see.

Combining Counts and Choosing the Right Function

You can add separate COUNTIF results to count records that meet either of two mutually exclusive categories. In the region list A2:A11 given earlier, North appears 4 times and East appears 3 times. With region names in A2:A11, this formula adds those counts and returns 7:

=COUNTIF(A2:A11,"North")+COUNTIF(A2:A11,"East")

This method is appropriate when the categories cannot overlap, as exact region names do. With wildcard conditions or other criteria that can match the same cell, adding counts may count a record twice. Think through whether the conditions overlap before interpreting the total.

Use COUNTIFS when a count must satisfy multiple conditions at once, such as region equals North and status equals Complete. COUNTIF is preferable for one clear condition because its formula is shorter and easier to audit. For totals rather than counts, SUMIF or SUMIFS may be more appropriate; for averages by condition, AVERAGEIF or AVERAGEIFS may fit the question. A PivotTable is useful when you want to explore many categories or several fields without writing a separate formula for each summary.

Common COUNTIF Problems to Check

Wrong range: Confirm that the range covers every intended data row and does not include unrelated headings or totals.
Extra spaces: “North” and “North ” can produce confusing results in exact-match analysis. Clean inconsistent source text where appropriate.
Numbers stored as text: A value that looks numeric may be text, affecting comparisons and data summaries.
Criteria punctuation: Comparison operators must be joined correctly to a reference, and text criteria must be enclosed in quotation marks.
Dates stored as text: Convert text dates to real dates before using date comparisons.
Case sensitivity: COUNTIF ignores letter case. It cannot distinguish North from NORTH; a case-sensitive count needs a different approach.
Only one condition: If a result must satisfy two or more tests, use COUNTIFS rather than trying to place multiple conditions inside one COUNTIF criterion.

When a formula returns zero unexpectedly, test the criterion against a few source cells and check for spaces, different spellings, or incompatible data types. When it returns too many matches, review wildcard use and whether a comparison includes boundary values. These checks are often more useful than changing the formula at random.

Frequently Asked Questions

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. It is suitable for ordinary category counts, but not for separating labels solely by capitalization.

Can COUNTIF check two conditions?
Not in a single COUNTIF criterion. Use COUNTIFS for conditions that must all be true, such as a matching region and a matching status.

How do I count cells that are not blank?
Apply the not-blank criterion to the range with "<>". If the result seems inconsistent, inspect cells that may contain spaces or formulas returning empty text.

Why does COUNTIF return zero for a date?
The cells may contain text rather than actual Excel dates, or the criterion may use the wrong date boundary. Check the underlying values and build comparison dates with DATE when practical.

Can I use COUNTIF for partial text?
Yes. Use an asterisk around the text to match it anywhere in a cell, such as “*tea*”. Remember that partial matching can include more labels than an exact match.

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.