COUNTIF Dropdown in Google Sheets: Count Selections

To count choices made in a Google Sheets dropdown, use COUNTIF on the cells containing the dropdowns. For example, if B2:B100 contains status selections, =COUNTIF(B2:B100,"Approved") counts the cells whose value is Approved.

What COUNTIF does with dropdowns

A dropdown is a way to control or suggest what people enter in a cell; it is not itself a counting tool. Once someone chooses an option, Google Sheets stores that choice as the cell’s value. COUNTIF checks a range of cells and counts those whose values meet one condition. That makes it useful for tallying statuses, categories, responses, or any other dropdown selection.

The key is to count the cells where people make selections, not the cells that hold the dropdown’s option list. If a dropdown offers Not started, In progress, and Done, COUNTIF can tell you how many task rows currently contain each status. It does not count how many options appear in the dropdown menu.

COUNTIF counts matching cells, not distinct people or records. If the same person appears on five rows and has selected Approved on three of them, a COUNTIF over those five status cells returns three. To count people, first decide how the data identifies a person and whether duplicate rows should be included.

Set up the dropdown range

If the dropdown already exists, you can skip this setup. In Google Sheets in a web browser, select the cells that should contain choices, then choose Data > Data validation and add a rule. Choose a dropdown with manually entered options or a dropdown based on a range, enter the choices, and select Done. The exact labels in the rule panel can vary as Google updates its interface.

A range-based dropdown takes its options from cells elsewhere in the spreadsheet. That can be easier to maintain when many people use the same list, because editing the source list updates the available choices. Whether options are entered directly or taken from a range, COUNTIF counts the selected values in the response cells.

Put the dropdown in a consistent column, such as B2:B100.
Use clear, consistent option text, such as Approved, Pending, and Rejected.
Consider whether people should be prevented from entering values outside the list. A validation rule can reject invalid entries or show a warning, depending on the selected setting.

Dropdown display styles, including chip-style displays, change how a value looks, not the basic counting approach. COUNTIF evaluates the cell’s value. If the visible option and the criterion do not match because of spelling, punctuation, or extra spaces, the count may be lower than expected.

Count one specific dropdown choice

Suppose the status dropdown is in B2:B8 and those cells contain Approved three times, Pending twice, Rejected once, and one blank. With statuses in that range, use this formula to count Approved; it returns 3:

=COUNTIF(B2:B8,"Approved")

The first argument is the range to inspect. The second is the criterion: here, the text that must match. Text criteria go inside straight double quotation marks. The spelling and punctuation should match the dropdown choice. COUNTIF is not case-sensitive, so approved and Approved are treated as equivalent for this purpose.

You can also put the criterion in a cell, which makes a small summary table easier to reuse. For example, if dropdown selections are in B2:B8 and D2 contains the text Approved, use this formula to count the value named in D2; with the example data above, it returns 3:

=COUNTIF(B2:B8,D2)

Here, D2 is a cell reference, so it is not enclosed in quotation marks. Changing D2 to Pending makes the formula count Pending instead. This approach is useful for a dashboard where a user can choose a category to inspect without editing the formula.

Count every option in a summary table

To show a total for each dropdown choice, list each option in a summary column and put a COUNTIF formula beside it. For instance, suppose B2:B100 contains the dropdown selections, and D2:D4 lists Approved, Pending, and Rejected. With the option name in D2 and the selection range in B2:B100, enter the formula below in E2; if B2:B100 contains 12 Approved selections, E2 returns 12:

=COUNTIF($B$2:$B$100,D2)

Copy the formula down beside the other options. The dollar signs keep the data range fixed as the formula is copied, while D2 changes to D3 and D4. The result is a simple count table that updates when selections change. Extend the range if new response rows are added, or use a range sized for the expected entries.

A useful check is to compare the sum of the category counts with the number of nonblank response cells. They should match if every nonblank cell contains exactly one of the listed options and the list includes every valid choice. A mismatch can reveal blanks, extra values, or a missing category in the summary.

Count using more than one condition

COUNTIF handles one condition. If the question depends on two or more columns—for example, how many Sales tasks are Approved—use COUNTIFS, which applies multiple criteria to corresponding ranges. Suppose A2:A20 contains departments and B2:B20 contains dropdown statuses. If two rows have Sales in column A and Approved in column B, this formula returns 2:

=COUNTIFS(A2:A20,"Sales",B2:B20,"Approved")

Each criteria range must cover the same rows so that the conditions are checked against the corresponding records. You can also refer to cells containing criteria instead of typing text into the formula. For example, a summary table could hold the department and status labels in separate cells, letting you change the requested combination without rewriting the calculation.

This is helpful for a tracker that records both a dropdown status and another attribute, such as team, region, or owner. It does not change the dropdown itself: it simply narrows the count to rows meeting all the specified conditions.

Criteria, blanks, and common counting problems

Most dropdown tallies use an exact text criterion, but COUNTIF also supports criteria such as numbers, comparisons, and wildcards. A question mark wildcard matches one character, while an asterisk matches any sequence of characters. For example, wildcards can be useful when you intentionally want to count text that contains or begins with a pattern. For ordinary dropdown options, exact matching is usually safer because similar labels can otherwise be counted together.

Check the range: Make sure the formula refers to the cells with user selections, rather than the source list of dropdown choices.
Check the label: Compare the criterion with the actual option, including spaces and punctuation. A criterion in a cell can also contain an unnoticed leading or trailing space.
Check the whole column: A formula that stops at row 100 will not count a selection entered in row 101. Expand the range when needed.
Check blanks: An empty dropdown cell is not a selected category. If blank rows are expected, avoid treating them as missing counts for a listed option.
Check unvalidated entries: A pasted value or an entry permitted by a warning-style rule may not be one of the listed options. Such a value will not be included in the count for any listed choice unless it matches that choice.

COUNTIF is case-insensitive, but it is not a general cleanup tool. If the sheet contains inconsistent wording, normalize the values or fix the validation source rather than relying on a broad wildcard. Also remember that a formula can return zero without being broken: there may simply be no exact matches in the selected range.

COUNTIF versus dropdowns and other options

Data validation and COUNTIF do different jobs. Data validation supplies or checks the choices people can enter; COUNTIF summarizes values already in cells. Creating a dropdown does not automatically create totals, and changing a dropdown option list does not by itself build a count table. Set up the validation rule and the summary formula separately.

For a quick count of one choice, COUNTIF is usually the simplest option. Use COUNTIFS when the count needs multiple conditions. A pivot table is another useful choice when you want to summarize many categories or add further grouping, such as counts by month and status. Pivot tables are more flexible for exploration, while a COUNTIF summary is often easier to place directly beside a small tracker.

In Microsoft Excel, the basic COUNTIF syntax and the idea of counting dropdown selections are similar, but the data-validation interface and menu paths differ. The instructions here are for Google Sheets in a web browser, not Excel. In Sheets, a validation dropdown is created from Data > Data validation; the formula can be entered in any suitable cell in the spreadsheet.

Frequently Asked Questions

Can Google Sheets automatically count dropdown selections?
Not just by creating a dropdown. Add a COUNTIF formula, or use a pivot table, to summarize the selected values. The result updates as the underlying cell values change.

How do I count how many cells are not blank?
For a dropdown response column, COUNTIF can count nonblank cells with the criterion "<>", but the range should contain only the response cells you intend to measure. If blanks or formulas that return empty text are important to your workflow, check the result against the actual sheet data, because how cells are populated affects what you mean by “answered.”

Can COUNTIF count a choice selected from a dropdown chip?
Yes. The chip is a visual display for the cell’s dropdown value. Use the same cell range and criterion you would use for a standard dropdown display.

Why does COUNTIF return zero even though I can see the choice?
Confirm that the formula points to the right cells and that its criterion matches the stored option. Look for extra spaces, different punctuation, or a range that ends before the visible selection. Also ensure the displayed cell is not outside the range being counted.

Can I count two dropdown choices together?
Yes. You can add separate COUNTIF results for the two exact options, or use a broader criterion where that is appropriate. Separate counts are easier to audit when the options are known and should remain distinct.

Does COUNTIF count the available options in the dropdown menu?
No. It counts matching cell values in the range you specify. To count the options themselves, apply COUNTIF to the cells that contain the source list, rather than to the cells where users make selections.

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.