COUNTIF in Excel: Count Yes or No

Formulas & functions · Updated November 22, 2026

Yes. Microsoft Excel has a COUNTIF function, and you can use it to count cells that contain “Yes,” “No,” or another value that meets a condition. Its basic form is =COUNTIF(range, criteria): give Excel the cells to check and the value or rule to match.

What COUNTIF means in Excel

COUNTIF is a worksheet function for counting cells that meet one condition. It is useful for survey answers, attendance lists, inventory labels, task statuses, and many other tables where you need a tally rather than a sum. For example, it can count how many orders are marked “Shipped” or how many test scores are above a threshold.

The name combines “count” and “if”: Excel counts a cell only if its content satisfies the criterion you specify. The function returns a number, including zero if no cells match. It does not change the source cells or create a separate summary table; it returns a count in the cell where you enter the formula.

COUNTIF is available in Microsoft Excel for Microsoft 365 on Windows, as well as other current Excel editions. It is also supported in Excel for Mac and Excel for the web. The menus and formula entry are broadly similar across these versions, so a basic COUNTIF formula does not require a Windows-only feature.

COUNTIF syntax and criteria

The syntax has two arguments: range, the cells Excel checks, and criteria, the condition a cell must meet. The range is commonly written as a cell reference such as A2:A100. A criterion can be text, a number, a comparison such as greater than a value, or a reference to another cell.

For a concrete example, suppose A2:A6 contains Open, Closed, Open, Pending, and Open, and C1 contains the word Open. With that layout, the formula is =COUNTIF(A2:A6,C1). It returns 3 because three cells in the range match the value in C1.

Text criteria typed directly into a formula need quotation marks; a criterion that refers to a cell does not. Excel does not distinguish uppercase and lowercase letters when COUNTIF compares ordinary text, so “open” and “Open” match the same way. However, extra spaces are still characters: a cell containing “Open ” with a trailing space may not match the criterion “Open”.

For comparison criteria, put the operator and value together inside quotation marks, or combine an operator with a cell reference using the ampersand. For example, the criterion “>100” means greater than 100, not greater than or equal to 100. That distinction matters when a value is exactly 100.

Count Yes and No responses

A common reason people search for “COUNTIF yes or no” is to tally responses in a Yes/No column. Suppose B2:B8 contains, in order, Yes, No, Yes, Yes, a blank cell, No, and Yes. This range has four Yes responses and two No responses; the remaining cell is blank.

With those responses in B2:B8, count Yes using =COUNTIF(B2:B8,"Yes"). The result is 4. With the same layout, count No using =COUNTIF(B2:B8,"No"). The result is 2. These are separate formulas because each COUNTIF formula checks one criterion.

To make the summary easier to reuse, enter Yes in D2 and No in D3. With the responses in B2:B8 and the criteria in D2:D3, enter =COUNTIF($B$2:$B$8,D2) beside D2 and copy it down beside D3. The first result is 4 and the second is 2. The dollar signs keep the response range fixed while the criterion reference changes as you copy the formula.

In a real workbook, check how responses are entered before trusting the total. If some cells contain “Yes – maybe,” a plain exact-text criterion of “Yes” will not count them. If users type inconsistent labels such as “Y,” “Yes,” and “yes,” decide whether those entries should count together and standardize the data or use an appropriate criterion.

Count numbers and partial text matches

COUNTIF also accepts numerical comparisons. Suppose D2:D6 contains 45, 120, 250, 90, and 150. With those numbers in D2:D6, =COUNTIF(D2:D6,">100") returns 3: 120, 250, and 150 are greater than 100. Change the criterion to “>=100” if values equal to 100 should also count.

For a threshold stored in another cell, say F1 contains 100, and the values remain in D2:D6, use =COUNTIF(D2:D6,">"&F1). It also returns 3. The ampersand joins the greater-than operator to the value in F1, which lets you change the threshold without editing the formula.

Wildcards let COUNTIF match part of a text value. The asterisk (*) represents any number of characters, while the question mark (?) represents one character. Suppose C2:C6 contains “Yes – urgent,” “Yes – later,” “No,” “Yes,” and a blank. With that layout, =COUNTIF(C2:C6,"Yes*") returns 3 because all three entries beginning with Yes match.

If the text you want to match includes a wildcard character itself, precede that character with a tilde (~) to treat it literally. For instance, a criterion containing ~* searches for an actual asterisk rather than using it as a wildcard. This is a less common need, but it can explain an unexpectedly broad match in data containing symbols.

When to use COUNTIFS instead

COUNTIF handles one condition. If you need every counted row to satisfy two or more conditions, use COUNTIFS, which applies multiple criteria to corresponding ranges. For example, suppose G2:G6 contains North, North, South, North, South, and H2:H6 contains Yes, No, Yes, Yes, No. With regions in G2:G6 and responses in H2:H6, =COUNTIFS(G2:G6,"North",H2:H6,"Yes") returns 2: two rows are both North and Yes.

COUNTIFS is not the same as asking for either of two conditions. Its criteria are combined as “and”: a row must meet every condition. If you want to count Yes or No in a single response column, calculate each count separately or add the two counts. Be careful when adding conditions that can overlap, since the same cell or row could be included more than once.

Use COUNTIF when the question is “How many cells meet this one rule?” Use COUNTIFS when the question includes multiple rules that must all be true for a row to count. For a small one-off count, a filter may also help you inspect matching records, but a formula is more convenient when the total should update as the source data changes.

Common COUNTIF problems

  • The result is zero: Check spelling, leading or trailing spaces, the selected range, and whether the criterion is in quotation marks when typed as text. A cell reference used as the criterion should point to the intended value.
  • The result seems too high: A wildcard such as * can match more text than expected. Also verify that the range does not include extra rows or duplicate records.
  • Numbers do not match as expected: Some values that look like numbers may be stored as text. Check the source data type, especially when values were imported from another system.
  • A copied formula changes its range: Use absolute references, such as $B$2:$B$8, when the range should stay fixed as the formula is copied.
  • The formula shows an error: Confirm that the parentheses and quotation marks are paired and that the function has both arguments. Some regional settings use semicolons rather than commas between formula arguments; if Excel rejects a comma-separated formula, check the separator expected by that installation.

COUNTIF can count empty cells when the criterion is an empty string. For example, suppose F2:F5 contains a value, a genuinely empty cell, another value, and a formula that displays an empty string. With that layout, =COUNTIF(F2:F5,"") returns 2. This behavior can be surprising: the function counts both the blank cell and the cell whose formula displays nothing. If you need to distinguish those cases, inspect the source cells or use a different test suited to the data.

Accuracy and practical limits

COUNTIF counts matching cell contents; it does not count based on formatting alone. A cell that is red because of conditional formatting is not counted merely because it looks red. If the color represents a status, count the underlying status value instead. Likewise, COUNTIF is not a distinct-count function: repeated matching entries each contribute to the total.

For reliable recurring counts, keep the data in a consistent column, use one spelling for each category, and avoid mixing notes with the values being counted. Excel tables can make ranges easier to maintain as rows are added, although the table setup is optional for a basic formula. After entering a formula, compare its result with a quick manual check on a small sample, especially if the workbook contains imported data or wildcard criteria.

Frequently Asked Questions

Does COUNTIF count Yes and No in Excel?
Yes. Use one COUNTIF formula with the criterion “Yes” and another with the criterion “No.” Each formula returns the number of cells matching that one response.

Is COUNTIF case-sensitive?
No. COUNTIF does not distinguish uppercase and lowercase text in its standard matching. It can still distinguish text with different spaces or other characters.

What is the difference between COUNTIF and COUNTIFS?
COUNTIF checks one condition. COUNTIFS checks multiple conditions together, requiring a row to meet all supplied criteria.

Can COUNTIF count cells containing a word within a longer phrase?
Yes. Use an asterisk wildcard around or after the word as needed. For example, *urgent* matches text containing “urgent” anywhere in the cell.

Why does COUNTIF return zero when the word looks correct?
Look for extra spaces, inconsistent entries, a wrong range, or a criterion that is not written or referenced correctly. Imported data may also contain values stored in a different form than they appear on screen.

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.