Google Sheets: Count Dropdown Values in a Column Easily

To count how many times a particular dropdown choice appears in Google Sheets, use =COUNTIF(B2:B100,"Approved"), replacing the range and choice with your own. To count all nonblank selections, use =COUNTIF(B2:B100,"<>"); to count distinct choices that have been selected, use =IFERROR(COUNTUNIQUE(FILTER(B2:B100,B2:B100<>"")),0).

What “count dropdown values” can mean

A dropdown is a data-validation rule that controls what someone can select in a cell. It does not keep a separate tally of selections. The choices are stored as ordinary cell values, so formulas count the contents of the cells just as they would count text entered by hand. The right formula depends on what you want the result to represent.

Occurrences of one choice: Count how many cells say “Approved,” “In progress,” or another specified value.
All completed dropdown cells: Count every cell with a selection, regardless of which option was chosen.
Distinct choices in use: Count how many different dropdown values appear at least once.
Choices offered by the dropdown: Count the options in its source list, which is different from counting selections in the response column.

For example, a list could offer five options while the sheet contains 40 selections and only three distinct choices in use. Those are three different counts, and each answers a different question.

Count occurrences of one dropdown choice

COUNTIF is the simplest way to count one specific value. Suppose cells B2:B100 contain status dropdowns and you want the number marked Approved. With dropdown selections in B2:B100, use:

=COUNTIF(B2:B100,"Approved")

If Approved appears 17 times in that range, the formula returns 17. The text in quotation marks must match the dropdown value. For another choice, replace Approved with the exact label, such as “Waiting” or “Complete.” The formula can go in any empty cell outside the response range; otherwise, it could interfere with the data being counted.

To make the count easier to maintain, put each option in a small summary table. For example, if the labels are in D2:D5 and the dropdown responses are in B2:B100, enter the following formula in E2 and fill it down:

With the option label in D2 and responses in B2:B100, use:

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

The dollar signs keep the response range fixed as you copy the formula, while D2 changes to D3, D4, and so on. This produces a count beside every listed choice, including zero for an option that has not been selected.

Count all nonblank selections

To find how many cells in a dropdown column have a selection, regardless of the selected label, use a nonblank criterion. With responses in B2:B100, enter:

=COUNTIF(B2:B100,"<>")

The result is the number of filled cells in the range. If 40 rows contain a selected value and the rest are empty, the formula returns 40. This is useful for measuring how many records have been completed, but it does not tell you which options are common.

COUNTA is another common way to count filled cells. With responses in B2:B100, the formula is =COUNTA(B2:B100). For ordinary dropdown entries, it will generally give the same result. One distinction matters if cells contain formulas: COUNTA counts a cell containing a formula even if that formula displays an empty string. A direct dropdown cell left empty is not the same as a formula cell that returns an empty string.

If you specifically want to count empty cells instead, use =COUNTBLANK(B2:B100) with the same range. Keep in mind that “empty” may not mean “not yet completed” if the sheet contains formulas, imported data, or other content that looks blank. Check a few cells if your totals do not match expectations.

Count distinct values that have been selected

A distinct count treats repeated selections as one value. For instance, if a column contains Approved, Approved, Waiting, and Approved, the distinct count is two. In Google Sheets, combine COUNTUNIQUE with FILTER to exclude blank cells. With responses in B2:B100, use:

=IFERROR(COUNTUNIQUE(FILTER(B2:B100,B2:B100<>"")),0)

FILTER passes only nonblank cells to COUNTUNIQUE, and COUNTUNIQUE counts the different values among them. IFERROR makes the result 0 when there are no nonblank entries, since FILTER has no matching rows to return in that situation. This formula counts choices actually used in the response range; it does not count every choice available in the dropdown menu.

For a visible list of the distinct responses rather than just a number, use =UNIQUE(FILTER(B2:B100,B2:B100<>"")) in an empty area. The results expand into cells below the formula, so leave that area clear. You can then count the displayed list or use the combined COUNTUNIQUE formula above when you only need a total.

Count the options offered in the dropdown

The dropdown’s available choices may come from a range in the spreadsheet or may be typed directly into the validation rule. A formula can count values in a source range, but a worksheet formula does not directly read the list of manually entered options from a cell’s data-validation settings.

If the dropdown uses a source list in E2:E20, and each option is entered once, count the filled options with:

=COUNTIF(E2:E20,"<>")

If that source range might contain duplicate labels and you want the number of distinct nonblank options, use:

=IFERROR(COUNTUNIQUE(FILTER(E2:E20,E2:E20<>"")),0)

In Google Sheets, you can inspect or edit the rule by selecting a cell with the dropdown and choosing Data > Data validation. The side panel shows the rule and its criteria, including whether it uses a dropdown list or a range. If the options were typed into the rule rather than stored in cells, count them in the rule itself or move them into a source range that you can maintain and count.

Build a useful count summary

A small summary table can turn raw selections into a quick report. Put each option in one column and its count in the next. For a status tracker, the summary might list Approved, Waiting, and Rejected, with a COUNTIF formula beside each label. The summary then updates as people change their dropdown selections, as long as the response range includes the relevant rows.

Use clear labels: Make the summary labels match the dropdown options so the formulas are easy to audit.
Allow for new rows: Use a range that covers the expected data, or use a suitable open-ended range such as B2:B if the sheet layout permits it.
Keep source options in one place: A source range makes it easier to update the dropdown and its count table consistently.
Separate totals from data: Put formulas in a summary area rather than inside the cells where users make selections.

For a changing list of categories, a summary table is often easier to understand than a long set of separate formulas. If you need a quick one-off breakdown, a pivot table is another option: select the response data and choose Insert > Pivot table, then use the dropdown column as a row field and as a summarized field. Pivot tables can be refreshed as the source data changes, and the exact setup depends on whether you want counts by category or another summary.

Why a count may look wrong

Most unexpected results come from the range or the underlying cell contents, not from the dropdown feature itself. Check these common causes before changing the formula:

The range misses some rows: A formula ending at B100 will not include a selection entered in B101. Expand the range or use an open-ended range where appropriate.
The displayed labels are not identical: Two choices that look similar may contain different punctuation or spacing. Use the actual dropdown label in the criterion or refer to a cell containing it.
Blank cells are being included or excluded unexpectedly: Decide whether you need a count of selections, empty cells, or all rows, and choose COUNTIF, COUNTBLANK, or another formula accordingly.
The formula counts values outside the dropdown: COUNTIF counts matching cell contents whether they came from a dropdown or were typed manually. It does not verify that a cell currently has a validation rule.
There are duplicate options in a source list: A basic filled-cell count counts both copies. Use a distinct count if you mean different labels rather than listed entries.

COUNTIF matching is not case-sensitive, so Approved and APPROVED are treated as the same text for this count. It also supports wildcard criteria: for example, with responses in B2:B100, =COUNTIF(B2:B100,"Open*") counts text values that begin with Open. Wildcards can be useful for related labels, but use an exact criterion when you mean one specific dropdown option.

When to use a formula, filter, or pivot table

Use COUNTIF when you need a reliable number for one category, or a small set of categories displayed in a summary. Use COUNTIF with “<>” for a basic completion total, and COUNTUNIQUE with FILTER when the question is how many different selections appear. These formulas recalculate as the underlying values change.

A filter is helpful when you want to inspect the matching records rather than only see a total. Select the data and use Data > Create a filter, then filter the dropdown column to a particular choice. A pivot table is more convenient for a larger category breakdown or a report that groups data. Neither method changes the meaning of the count: both summarize the cell values, not the validation rules that created the dropdown.

Google Sheets formulas and menus are not Excel-specific instructions. Excel has similar counting functions, but its interface and some formula behavior can differ. The formulas shown here are for Google Sheets in a web browser.

Frequently Asked Questions

Can Google Sheets count how many times each dropdown option appears?
Yes. List the options in cells and use COUNTIF beside each one, referring to the option cell as the criterion. Fill the formula down to get a count for every listed choice.

How do I count dropdown cells that have not been filled in?
Use =COUNTBLANK(B2:B100), changing the range to match your dropdown column. Check whether the range contains formulas that display blank text, because formula results can affect how a blank count behaves.

Does COUNTIF count only cells with dropdown validation?
No. COUNTIF checks cell contents, not data-validation settings. If someone types the same text into a cell without using its dropdown, that value can still be counted.

How can I count only different dropdown values?
Use =IFERROR(COUNTUNIQUE(FILTER(B2:B100,B2:B100<>"")),0). It removes blank entries before counting distinct values and returns 0 if the range contains no selections.

Can I count the choices in a dropdown menu automatically?
You can count choices stored in a source range with COUNTIF or COUNTUNIQUE. A formula does not directly extract manually typed choices from the data-validation rule; inspect that rule under Data > Data validation or store the options in a worksheet range.

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.