COUNTIF in Excel: Syntax, Examples, and Common Fixes

Formulas & functions · Updated November 18, 2026

In Excel, use COUNTIF to count cells in one range that meet one condition. If the values you want to check are in B2:B8, for example, enter =COUNTIF(B2:B8,"Paid") to count cells containing Paid; the function returns 3 if Paid appears three times. COUNTIF is useful for quick summaries of text, numbers, dates, and blank cells.

What COUNTIF Does

COUNTIF checks each cell in a specified range against a criterion, then returns the number of cells that match. It counts matching cells rather than adding their values. That makes it a straightforward way to answer questions such as how many orders are pending, how many scores exceed a threshold, or how many entries are missing.

The function has two arguments: the range to inspect and the criterion to apply. The criterion can be a value, text, a comparison such as greater than a number, a wildcard pattern, or a reference to another cell. COUNTIF applies that one condition across the range. If you need every match to satisfy two or more conditions at the same time, COUNTIFS is usually the better choice.

COUNTIF is available in Excel for Microsoft 365 on Windows, as well as current Excel for Mac and Excel for the web. The function works the same way across these versions. Depending on your regional settings, Excel may expect semicolons rather than commas between arguments; use the separator your installation accepts.

Count Text and Exact Values

A basic COUNTIF is often all you need when counting a category. In the example below, the range contains Paid, Pending, Paid, Paid, a blank cell, Unpaid, and Pending. The word Paid occurs three times. COUNTIF is not case-sensitive, so it treats paid and Paid as the same text for this purpose.

With those order statuses in B2:B8, enter =COUNTIF(B2:B8,"Paid"). The result is 3. Text criteria need quotation marks when typed directly into a formula. If the criterion is in a worksheet cell instead, refer to the cell without putting quotation marks around that reference.

With the status to count entered in D1 and the status data in B2:B8, use =COUNTIF(B2:B8,D1). If D1 contains Paid, the result is 3. This approach is handy when a report needs to count different categories over time: change the value in D1 rather than editing the formula.

COUNTIF compares text values as whole cell contents unless you include wildcard characters. Thus, a criterion of Paid does not count Unpaid. It also does not trim leading or trailing spaces: a cell containing Paid followed by a space may not match the criterion as expected. If a count seems too low, check for inconsistent spelling and extra spaces in the source data.

Count Numbers and Apply Comparisons

For numbers, COUNTIF can count an exact value or use a comparison operator. Common operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). When an operator is part of a criterion typed directly into a formula, place the operator and comparison value together inside quotation marks.

Suppose C2:C8 contains 12, 18, 18, 25, 31, 18, and 40. To count values greater than 20, enter =COUNTIF(C2:C8,">20"). The result is 3 because 25, 31, and 40 meet the condition. The comparison is strict: a value exactly equal to 20 would not be included.

You can also put the threshold in a cell, which makes it easier to adjust. With the same values in C2:C8 and a threshold of 20 in E1, use =COUNTIF(C2:C8,">"&E1). The result is also 3. The ampersand joins the comparison operator to the value in E1, creating the criterion that COUNTIF evaluates.

For an exact numeric match, use the number as the criterion rather than adding quotation marks around it. With the values above in C2:C8, =COUNTIF(C2:C8,18) returns 3. A common misconception is that COUNTIF adds the matching values; it does not. To total values that meet a condition, use SUMIF instead.

Use Wildcards to Match Text Patterns

Wildcards let COUNTIF find text that follows a pattern. An asterisk (*) represents any number of characters, including none. A question mark (?) represents exactly one character. These are useful when entries share a beginning, ending, or partial spelling but are not identical.

Suppose A2:A7 contains Ana, Ann, Anne, Joann, Anna, and Bob. To count names that begin with An, use =COUNTIF(A2:A7,"An*"). The result is 4: Ana, Ann, Anne, and Anna. The asterisk allows any characters to follow the letters An.

With the same names in A2:A7, =COUNTIF(A2:A7,"?nn") returns 1, matching Ann: the question mark allows one character before nn. To find a literal asterisk or question mark in cell contents, precede that character with a tilde (~) in the criterion. For example, a criterion containing ~* searches for an actual asterisk rather than treating it as a wildcard.

Wildcards are not a substitute for careful data cleaning. A pattern can match more records than expected, especially when it is broad. Before relying on a pattern-based count, inspect the values that should match and consider whether the pattern might include unintended entries.

Count Dates and Blank Cells

Excel stores real dates as serial numbers, so COUNTIF can compare dates using the same operators used for numbers. The important condition is that the source cells contain actual Excel dates, not text that merely looks like a date. Date formats affect how values appear, but the underlying values determine whether a comparison works.

Suppose F2:F6 contains the actual dates January 5, January 12, February 3, February 17, and March 1, 2026. To count dates in February, use two conditions with COUNTIFS, which is designed for multiple criteria. With those dates in F2:F6, enter =COUNTIFS(F2:F6,">="&DATE(2026,2,1),F2:F6,"<"&DATE(2026,3,1)). The result is 2: February 3 and February 17 are on or after February 1 and before March 1. Using a less-than condition for the next month is a reliable way to include all dates in February.

COUNTIF can also count cells that Excel treats as blank. With entries in B2:B8, =COUNTIF(B2:B8,"") counts blank cells, including cells whose formulas return an empty string. That detail can matter in a worksheet where formulas make cells look empty. To count cells that are not blank, use the criterion "<>"; check the source formulas if the result does not match what you see, because a formula returning an empty string can behave differently from a genuinely empty cell in some counting scenarios.

Choose Between COUNTIF and Related Functions

COUNTIF is appropriate when one condition is enough. For example, counting all rows marked Paid requires one criterion. When a count depends on multiple conditions—for instance, orders that are both Paid and from a particular region—COUNTIFS can apply criteria to multiple ranges. Its conditions are combined, so a row must satisfy all of them to be counted.

Choose the function that matches the task:

  • COUNTIF: counts cells meeting one condition in one range.
  • COUNTIFS: counts records meeting multiple conditions across corresponding ranges.
  • COUNT: counts cells containing numbers, without applying a criterion.
  • COUNTA: counts cells that are not empty, including cells containing text.
  • COUNTBLANK: counts blank cells, without a more specific criterion.
  • SUMIF: adds values that meet one condition rather than counting matching cells.

For multi-condition counts, keep the criteria ranges the same size and aligned by row. If a status range covers rows 2 through 100 but a region range covers rows 2 through 99, the result may be an error or an unintended count. Consistent range boundaries make formulas easier to check and maintain.

Common COUNTIF Problems and Fixes

A result of zero does not always mean the criterion is wrong. It can indicate that the displayed values differ from the underlying values. Text may contain extra spaces, numbers may be stored as text, or dates may have been imported as text. Check a few source cells directly and make sure their data types are consistent before rewriting the criterion.

When a criterion uses a comparison operator from a cell, remember to join the operator and the cell reference. Typing the operator in a separate quoted string without joining the threshold will not create the intended comparison. Also check that the criterion cell contains the value you expect and does not contain an accidental space.

Another common issue is confusing an exact match with a partial match. A criterion such as Paid looks for that value, while a pattern such as Paid* can also match text that begins with Paid and continues with other characters. If the result is unexpectedly high, check whether wildcard characters are present in the criterion or in the data.

COUNTIF is case-insensitive. If a task genuinely needs to distinguish Paid from PAID, COUNTIF alone cannot make that distinction; use a case-sensitive approach based on EXACT, often combined with SUMPRODUCT. Finally, when auditing a count, filter or inspect the matching rows as well as checking the returned total. That helps catch duplicates, misspellings, and data that only looks consistent.

Frequently Asked Questions

What is the basic COUNTIF formula in Excel?
COUNTIF takes the range to check and one criterion. For example, with statuses in B2:B8, =COUNTIF(B2:B8,"Paid") counts cells that match Paid. The function returns a number, not the matching cell contents.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive, so Paid, PAID, and paid match the same text criterion. If capitalization must be distinguished, use a case-sensitive method such as a formula involving EXACT rather than relying on COUNTIF by itself.

How do I use COUNTIF with a cell reference?
Put the criterion in a cell and use that cell as the second argument. For a comparison such as greater than a threshold, join the operator to the threshold reference with an ampersand. This lets you update the criterion without editing the formula.

Can COUNTIF count cells that contain part of a word?
Yes. Use an asterisk for any number of characters or a question mark for one character. For example, An* matches text beginning with An. Remember that wildcards can include more entries than an exact-match criterion.

Why does COUNTIF return zero when the values look identical?
Look for extra spaces, inconsistent data types, or dates stored as text. Also verify the exact criterion and whether you need a wildcard. A cell displaying a date or number may not contain the same underlying type as the criterion being compared.

Make Counts Easier to Maintain

For a formula that will be copied or reused, use a clearly labeled criterion cell and keep the data range consistent. If new rows will be added regularly, consider converting the source data into an Excel table so its range can expand as rows are added. A well-labeled count, such as Pending orders, also makes a summary easier for another person to interpret.

Finally, treat a COUNTIF result as a calculation based on the data currently in the selected range—not as a check that the data itself is complete or correct. Validate the range, review unusual entries, and choose COUNTIF, COUNTIFS, or a summing function according to the question you actually need to answer.

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.