Google Sheets COUNTIF: Count Yes Answers

To count cells that contain the answer Yes in Google Sheets, use =COUNTIF(A2:A100,"Yes"), replacing A2:A100 with your response range. COUNTIF counts matching cells without regard to capitalization, so Yes, YES, and yes all qualify.

The basic COUNTIF method

COUNTIF checks a range against one condition and returns the number of cells that meet it. In the example above, responses are in A2:A100 and the criterion is the text Yes. The range includes row 100, so a Yes added in that row will be included; answers outside the range will not be counted.

For a small list, enter the formula in an empty cell outside the response range, such as B1. Google Sheets displays the resulting count there and recalculates it when matching values in the range change. If the formula is placed inside the range it counts, the result can include itself as a circular reference, so keep the summary cell separate.

If the word to count is stored in a cell, you can refer to that cell as the criterion. With responses in A2:A100 and the word Yes in D1, enter =COUNTIF(A2:A100,D1). This makes the summary reusable: edit D1 to count another response without rewriting the formula. The criterion cell should contain the value itself, not a formula-like phrase or an extra space.

How COUNTIF treats Yes

COUNTIF uses the pattern COUNTIF(range, criterion): the first argument identifies which cells to inspect, and the second describes what qualifies. The function compares the underlying cell contents, not simply what appears visually on the sheet. For example, a cell whose formula returns the text Yes can be counted, while a cell formatted to look like Yes but containing a different underlying value may not be.

Text matching is case-insensitive. You do not need separate counts for Yes, YES, and yes. However, added spaces can matter: a value such as Yes followed by a space may not behave like a clean Yes match. Data imported from forms, copied from websites, or assembled from other spreadsheets can contain such invisible characters.

COUNTIF also supports wildcard criteria. An asterisk represents any sequence of characters, and a question mark represents one character. With responses in A2:A100, this formula counts any cell whose text contains yes somewhere, including answers such as Yes, absolutely:

=COUNTIF(A2:A100,"*yes*")

That broader match has a trade-off: it can count words that merely contain those letters, such as yesterday. Use the plain Yes criterion when you mean a complete answer, and use wildcards only when partial matches are intentional.

Count exact answers versus answers containing Yes

For a survey where each response should be exactly Yes or No, the basic formula is usually the right choice. It avoids counting a sentence that mentions yes but is not itself an affirmative response. If the cells may contain longer comments and you want to count any comment mentioning the word, a wildcard can be useful, but it does not understand word boundaries or meaning.

For instance, with comments in A2:A100, =COUNTIF(A2:A100,"*yes*") counts any text containing those letters, regardless of whether the comment says yes, yesterday, or something else. Review the source data before treating that total as a count of affirmative people. A spreadsheet function matches character patterns; it cannot decide whether a response is genuinely positive or whether a negation changes its meaning.

When responses need to be standardized, consider using a dropdown rather than asking people to type freely. Select the response cells, then use Insert > Dropdown in Google Sheets to create controlled choices such as Yes and No. This reduces variations like Y, Yep, or Yes, thanks, which require separate decisions about what should count.

Count checked checkboxes

A checkbox in Google Sheets is not stored as the text Yes. By default, an unchecked checkbox evaluates to FALSE and a checked one to TRUE. Therefore, counting checked boxes requires TRUE as the criterion, not the word Yes.

With checkboxes in B2:B100, enter =COUNTIF(B2:B100,TRUE) to count the checked boxes. Insert checkboxes by selecting the cells and choosing Insert > Checkbox. If the checkbox uses custom values, such as Yes for checked and No for unchecked, count the custom checked value instead; the displayed checkbox state alone does not tell you what value its cell contains.

This distinction is useful when converting a form or checklist into a summary. If one column contains a typed Yes/No answer and another contains a checkbox, each column has a different underlying value type. Matching the criterion to the actual cell contents prevents a formula that looks reasonable but always returns zero.

Count Yes alongside another condition

When the question is not simply how many Yes answers exist, use COUNTIFS to apply more than one condition. For example, suppose column A contains a response and column B contains a department. With responses in A2:A100, departments in B2:B100, and the department name Sales in D1, use =COUNTIFS(A2:A100,"Yes",B2:B100,D1). The result counts rows where the response is Yes and the department matches D1.

Every range supplied to COUNTIFS should cover the same number of rows. A2:A100 and B2:B100 align; using A2:A100 with B2:B50 can produce an error because the criteria ranges have different dimensions. Add further range-and-criterion pairs when you need additional filters, such as a region, date, or category.

COUNTIFS is an AND test: a row must meet every listed condition to be counted. If the goal is to count Yes responses in either of two separate categories, a single pair of criteria may not express the intended OR logic. You can add separate counts or arrange the source data and criteria deliberately, then verify a few rows manually to confirm that the total matches the question being asked.

Clean inconsistent responses and troubleshoot zero counts

A result of zero does not always mean there are no affirmative answers. First check that the range points to the response column and includes the relevant rows. Next inspect a cell that appears to say Yes: it may contain a space, a different phrase, an imported nonprinting character, or a checkbox value rather than text.

For ordinary extra spaces at the beginning or end of a response, a helper column is a transparent fix. If the original response is in A2, enter =TRIM(A2) in B2 and fill it down. TRIM removes leading and trailing spaces and reduces repeated spaces between words to one. Then count the cleaned values in B2:B100 with =COUNTIF(B2:B100,"Yes"). This method leaves the original data intact so you can compare it with the cleaned version.

When you use the helper-column method, keep the formula and count ranges consistent with the rows actually used. If new responses may be added below row 100, expand the range or use a suitable open-ended column range, such as A2:A, after checking that the formula cell is not in that same range. Open-ended ranges are convenient for growing lists, but they make it especially important to place summary formulas elsewhere.

Also check whether a filter is hiding rows. COUNTIF counts matching cells in its specified range whether those rows are currently visible or hidden by a filter. If you need a count limited to visible rows, COUNTIF alone is not a visibility-aware count; use a method designed for filtered data or create a filtered view of the rows before summarizing them.

Choose a reliable layout for a growing sheet

For a simple tally, keep one response per row and one response field per column. Put the count in a separate summary area with a clear label, such as Yes responses. This makes it easier for someone else to understand what the total measures and reduces the risk of including headings or notes in the range.

Use a dropdown for a fixed set of permitted answers, or checkboxes when the choice is truly a checked/unchecked state.
Keep the criteria visible in a labeled cell when you expect to reuse the count for different answers.
Use COUNTIFS when the count needs to be limited by another column, and ensure its ranges align row for row.
Check a sample of matching and nonmatching records before sharing a result, especially when the data was imported.

These habits matter because the formula can be correct while the question or range is wrong. A count of Yes answers is not necessarily a count of people if one person can submit multiple rows, and it is not necessarily a count of completed responses if blank cells are present. State what the count represents alongside the result.

Google Sheets and Excel differences

COUNTIF and COUNTIFS are available in both Google Sheets and Microsoft Excel, and the basic formulas shown here work in both programs with standard comma separators. This answer uses Google Sheets in a web browser, where menus such as Insert > Checkbox and Insert > Dropdown are Google Sheets paths. Excel has its own interface and checkbox options, so menu instructions should not be assumed to match exactly.

One practical difference is collaboration and data collection: a Google Sheet may receive edits from several people or responses from a linked Google Form. The count updates as the sheet changes, but the formula still only examines the cells in its specified range and follows the values stored there. If a form changes its response columns or adds a new field, check that the formula still points to the intended column.

Frequently Asked Questions

Does COUNTIF in Google Sheets ignore uppercase and lowercase?
Yes. A criterion of Yes matches Yes, YES, and yes. It does not make the function a semantic search, however; punctuation, extra text, or spaces can change whether a cell matches.

How do I count Yes in an entire column?
Use an open-ended range such as A2:A in the COUNTIF formula to include responses from row 2 onward. Keep the formula cell outside that range, and exclude the header row by starting at row 2.

Why does COUNTIF return zero when I can see Yes?
Confirm that the formula points to the right cells and that the value is text rather than a checkbox state. Look for extra spaces or added words, and try a helper column with TRIM if ordinary spaces are the problem.

Can COUNTIF count a checked checkbox?
Yes, but a standard Google Sheets checkbox stores TRUE when checked and FALSE when unchecked. Use TRUE as the criterion. If the checkbox has custom values, use the actual custom checked value.

How can I count Yes only for one team or category?
Use COUNTIFS with one condition for the Yes response and another for the team or category. Make sure both criteria ranges start and end on the same rows so each condition refers to the same record.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.