To count cells that contain the answer Yes, enter a COUNTIF formula in an empty cell. If your answers are in A2:A10, use =COUNTIF(A2:A10,"Yes"); for a list with five Yes entries, Excel returns 5. Replace the range with the cells in your worksheet. This counts Yes regardless of capitalization, but extra spaces or different wording can affect the result.
What You’ll Need
- A workbook open in Microsoft Excel for Microsoft 365 on Windows. The same COUNTIF formula works in Excel for Mac and Excel for the web, though ribbon layouts can vary.
- A column containing the responses you want to count. It can include other answers and blank cells.
- An empty cell outside the response list for the result. Keeping the result separate makes it easier to read and avoids including the total in the range.
Step-by-Step
- Identify the response range. Find the first and last cells that contain answers. For example, if the header is in A1 and responses begin in A2 and finish in A100, the range is A2:A100. Excluding the header prevents it from being counted if its text happens to match the criterion.
- Choose a result cell. Click a blank cell where you want the total to appear, such as B2. Avoid putting the formula inside the response range. If you place it in the list itself, the range may include the formula cell or become harder to manage as the data changes.
- Enter the COUNTIF formula. With responses in A2:A10, type
=COUNTIF(A2:A10,"Yes")in the blank result cell and press Enter. COUNTIF takes the range to inspect and the criterion to count. The quotation marks tell Excel that Yes is text. - Check the result against the data. In a sample list where A2:A10 contains Yes, No, Yes, a blank, yes, Pending, No, Yes, and Yes, the formula returns 5. COUNTIF is not case-sensitive, so yes and YES count along with Yes. The blank and other answers do not match.
- Adjust the range for your worksheet. If your responses are in C5:C250, enter a formula using that range instead. With responses in C5:C250, use
=COUNTIF(C5:C250,"Yes"). The range endpoints should cover all the data rows but not unrelated cells. - Include future entries if the list will grow. One option is to use a larger range than the current data, such as A2:A500, so new responses are counted without editing the formula. With current and future responses in A2:A500, use
=COUNTIF(A2:A500,"Yes"). Alternatively, convert the list to an Excel table: select a cell in the data, choose Insert > Table, confirm whether the table has headers, and use its response column in the formula. Structured references can expand with the table as new rows are added. - Confirm that Excel recalculates the result. After you change an answer in the range, the total should update automatically. If it does not, check Formulas > Calculation Options and make sure Automatic is selected. You can also press F9 to recalculate formulas. On a Mac, the formula itself is the same; use the Excel for Mac calculation controls if you need to change calculation settings.
For a quick one-time check, you can also select the answer cells and use Find. On Windows, press Ctrl+f, search for Yes, and review the matches; on Mac, press Command+f. Searching is useful for locating entries, but COUNTIF is usually better when you need a visible total that updates as the data changes.
Common Mistakes
- Including the header by accident. A header such as Yes could add one to the count. Start the range on the first response row, not the heading row. If your header is A1, A2 is often the correct starting point.
- Using a partial range. A formula that ends at row 50 will not count Yes entries in row 51 or below. Check both endpoints after entering the formula, especially if the column is longer than one screen.
- Expecting capitalization to matter. COUNTIF treats Yes, yes, and YES as the same text for this criterion. It cannot distinguish them just by changing the capitalization in the formula.
- Confusing an exact answer with text that contains the word. The criterion Yes counts cells whose content is Yes, not answers such as Yesterday or Yes, probably. If the worksheet uses longer phrases, decide whether you want exact matches or any cell containing the word before choosing a criterion.
- Leaving spaces in an answer. A cell containing Yes followed by a space may not match the criterion Yes. Spaces copied from another source can be difficult to notice on screen, so inspect suspicious cells or clean the data.
- Typing the criterion without quotation marks. For text, the criterion must be written as “Yes” inside the formula. Without the quotes, Excel may interpret the entry differently and display an error or an unexpected result.
- Putting the total inside its own range. Keep the formula cell outside the cells being counted. This avoids confusing the range and helps prevent circular-reference problems if the range later changes.
Tips
- Use one consistent response format. A dropdown can reduce variations such as Y, Yes, YES, and Yes – confirmed. Select the response cells, then choose Data > Data Validation and set Allow to List. Enter the allowed choices as the source, such as Yes,No,Pending. This is most useful when people will continue adding responses.
- Keep the header and total clear. Label the result cell Count of Yes, and place it beside or above the data rather than in the answer column. A label helps other people understand what the number represents when they open the workbook.
- Use a bounded range for a modest list. A range such as A2:A500 is easy to review and does not scan every row in the worksheet. For a continuously growing dataset, an Excel table is often easier to maintain than repeatedly extending a fixed range.
- Make the total easy to spot. Apply bold formatting or a subtle fill color to the result cell. Formatting does not change the count, but it can make a small summary more visible on a busy sheet.
- Keep Yes as plain text. If answers come from a survey export or another system, check that the cells really contain the text Yes rather than a similar phrase or a different value. The formula counts the stored cell content, not how you intended to label it.
Troubleshooting
The formula returns 0, but Yes is visible. First verify that the formula range covers the cells where Yes appears. Then click one of the visible answers and inspect its contents in the formula bar. It may contain a leading or trailing space, a nonprinting character, or different wording. Re-enter one answer manually as Yes to see whether that cell is counted. If the imported data includes ordinary extra spaces, make a cleaned helper column with TRIM, then count the cleaned results. With original responses in A2:A100, enter =TRIM(A2) in B2 and fill down; then, with the cleaned responses in B2:B100, use =COUNTIF(B2:B100,"Yes"). TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words, but it does not remove every possible nonprinting character.
The total is one or more too high. Check whether the range includes a header or a cell outside the response list. Also remember that COUNTIF is not case-sensitive: a lowercase yes is included. If you need to count only cells containing the exact text Yes while excluding lowercase versions, COUNTIF alone is not case-sensitive, so you will need a different method or standardized data.
Excel shows an error instead of a number. Check that the formula begins with an equals sign, the range uses a colon between its first and last cell, and the criterion is enclosed in straight double quotation marks. The formula should look like =COUNTIF(A2:A100,"Yes"). Excel installations configured for a different regional list separator may require semicolons instead of commas; if Excel rejects the comma version, check the separator used by your local Excel settings.
The result does not change after editing an answer. Confirm the edited cell is inside the formula range. If it is, check Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may wait for a recalculation command. Also make sure the result is a formula, not a number pasted over the formula cell.
The formula appears as text in the cell. The cell may be formatted as Text, or there may be an apostrophe before the equals sign. Change the cell format to General, edit the formula, and press Enter again. Simply changing the format may not convert an existing text entry until you re-enter it.
Frequently Asked Questions
Does COUNTIF count lowercase yes?
Yes. COUNTIF text matching is not case-sensitive, so Yes, YES, and yes all count for the criterion “Yes”. If capitalization is inconsistent, you do not need a separate criterion for each case.
Can I count Yes in an entire column?
Yes. With responses anywhere in column A, including a possible header, use =COUNTIF(A:A,"Yes"). A header that says Yes would be counted, so a bounded range such as A2:A100 is safer when you know where the responses start. A whole-column formula can also scan more cells than necessary.
How do I count cells that contain Yes as part of a longer answer?
With responses in A2:A100, use =COUNTIF(A2:A100,"*Yes*"). The asterisks are wildcards, so this counts any cell containing Yes somewhere in its text, including phrases such as Yes, probably. It may also match unrelated words that contain those letters, so use it only when that broader match is intended.
Will blank cells affect the count?
No. A blank cell does not match the criterion “Yes”, so it is ignored. Cells that look blank but contain spaces also do not count as Yes, though they can make the data harder to clean or interpret.
Can I use this formula in Excel for Mac or Excel for the web?
Yes. COUNTIF and the formula syntax shown here work in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The placement of ribbon commands and calculation settings may differ between platforms, but you do not need a different formula.
Can COUNTIF tell me how many Yes answers are visible after filtering?
COUNTIF counts matching cells in its range even when some rows are hidden by a filter. If you need a total that responds only to visible rows, use a method designed for filtered data rather than assuming this COUNTIF result excludes hidden entries.
Related
- How to Combine CSV Files in Excel with Power Query
- How to Color Values in Excel with Conditional Formatting
- Color Heat Rank in Excel with Conditional Formatting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUBTOTAL COUNTIF: Count Matching Visible RowsExcel has no built-in function that combines SUBTOTAL and COUNTIF: SUBTOTAL can ignore filtered-out rows, but it cannot…
- Excel COUNTIFS Multiple Criteria in the Same ColumnYes. In Excel, use COUNTIFS with the same range repeated for each condition; the conditions are combined with…
- Split a Cell in Excel: Text to Columns, Formulas, and MoreYes. In Microsoft Excel, select the cells and choose Data > Text to Columns to split their contents…
- Average Time Between Dates in Excel: Formula & ExamplesTo calculate the average time between consecutive dates in Excel, subtract each date from the one below it,…
- ROUNDUP Excel Cell Reference: 10 Useful Formula PatternsWith 12.31 in A2, enter =ROUNDUP(A2,0) to return 13; the formula reads the number from a cell and…
- XLOOKUP in Excel: What If the Return Cell Is Blank?If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.