How to Count Dropdown Values in Google Sheets

To count a particular dropdown choice in Google Sheets, use COUNTIF: if the dropdown answers are in B2:B100, enter =COUNTIF(B2:B100,"Approved") to count cells whose value is Approved. To count several choices, make a summary table and use the same function once for each label.

What a dropdown count measures

A Google Sheets dropdown limits or suggests what people can enter in a cell, but it does not automatically create a tally. A selection is stored as the cell’s value, so a counting formula looks at the cells containing responses, not at the dropdown rule itself. This distinction matters when the dropdown options are maintained in a separate range: that source range lists possible choices, while the response range contains the selections you want to count.

For example, imagine a project tracker where B2:B100 contains a Status dropdown with the options Not started, In progress, and Complete. A count of Complete tells you how many rows currently have that status. It does not count how many times the word Complete appears in the list of permitted options, nor does it count options that nobody selected.

Dropdowns may appear as colored chips or as arrows in the cells. The visual style does not change the count: formulas use the underlying cell value. If the cell displays Complete, a criterion of Complete can count it.

Count one dropdown choice

First identify the cells where responses are entered. Then choose a cell outside that response range for the result, such as D2, and enter a COUNTIF formula. With responses in B2:B100, this formula counts the Approved choice:

=COUNTIF(B2:B100,"Approved")

COUNTIF takes a range and a criterion. In this example, the first argument is the range to inspect and the second is the text to match. The result updates when a response is added, changed, or removed. You can put the answer beside a report, above a chart, or on a separate summary sheet; the formula works as long as its range points to the response cells.

For text choices, type the label inside straight double quotation marks. The wording should match the dropdown value, including spaces and punctuation. COUNTIF text matching is not case-sensitive, so Approved and APPROVED are treated as the same text for this count. A typo or an extra space can still prevent a match.

Count every option in a summary table

To compare all dropdown choices, list each option in one column and count it in the next. For example, put Approved, Pending, and Rejected in D2:D4. With responses in B2:B100 and the option label in D2, use:

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

Fill the formula down through E4. The dollar signs keep the response range fixed while the criterion changes from D2 to D3 and D4. This produces a compact frequency table that can be checked at a glance and used as the source for a chart. If your options are in a different order, the counts still match the text in each row.

It is usually safer to copy the option labels from the dropdown’s source list or type them once and reuse them than to retype labels in many formulas. A one-character difference in a label can make a count look like zero even when a similar-looking response is present. Keep the option names consistent across the validation list and summary table.

Create and maintain the dropdown list

In Google Sheets in a web browser, select the cells for responses and use Insert > Dropdown, or open Data > Data validation to configure the rule. Depending on the way the dropdown is set up, you can enter choices directly or use a range of cells as the source. The dropdown setup controls what users can select; the COUNTIF formula separately reports what they did select.

A source range is useful when options may change. For instance, place status names in F2:F4 and use that range in the dropdown rule. You can also use those same labels as the summary-table criteria, keeping the labels in one maintained list. If the source list grows, check that both the validation rule and your count table include the new option; changing one does not necessarily expand the other automatically.

When a dropdown is configured to reject invalid entries, users generally have to choose an allowed value or clear the cell. If it is configured to show a warning, other values may be entered. COUNTIF can still count the expected options, but values outside the list will not be included in any of those category counts. That can be useful for finding inconsistent data rather than silently treating it as a valid choice.

Count completed selections and blanks

Sometimes the question is not how many times each option appears, but how many responses have been made. If B2:B100 is the response range and the dropdown cells are otherwise empty until someone chooses an answer, use:

=COUNTA(B2:B100)

COUNTA counts nonempty cells, so it gives the number of filled response cells in that example. It does not check whether each value belongs to the dropdown list. If invalid entries are allowed, they are still counted as nonempty. Compare this result with the sum of the category counts to notice whether responses contain unlisted values or whether your summary has omitted an option.

To count empty cells in the same response range, use =COUNTBLANK(B2:B100). A blank cell usually means no answer has been recorded, rather than a selected dropdown choice. If a dropdown includes a visible choice such as Not applicable, choosing that option is different from leaving the cell empty: COUNTA counts the chosen label, while COUNTBLANK counts genuinely empty cells. Decide which meaning is appropriate for your report before labeling a total as “answered.”

Count by more than one condition

For a tracker with another field, such as department or owner, COUNTIFS can count rows that meet multiple criteria. Suppose department names are in A2:A100 and dropdown statuses are in B2:B100. To count Approved rows for Sales, use:

=COUNTIFS(A2:A100,"Sales",B2:B100,"Approved")

COUNTIFS applies each condition to its corresponding range, and the ranges should cover the same rows. This is handy for a dashboard that reports status by team, region, or month rather than one overall total. If a date column is included, it can also be used as another condition, provided the dates are real spreadsheet dates and the criteria are constructed appropriately.

For example, a summary table might place team names down the rows and status labels across the columns. Each intersection can use COUNTIFS with a row’s team name and a column’s status label. That makes a basic cross-tabulation without changing the dropdown values. Keep the criteria references connected to the table labels when possible, so the formula can be filled across and down instead of rewritten for every combination.

Common reasons a count looks wrong

If the formula returns zero or an unexpected number, check the response range and the actual stored values before changing the formula. Common causes include:

Wrong range: The formula may omit newer rows, point to the option-source list instead of the response cells, or include unrelated cells.
Inconsistent text: A trailing space, different punctuation, or a slightly different label can stop a match. Case alone is not normally the issue for COUNTIF.
Invalid entries: A dropdown warning can allow values that are not among the listed choices, so category totals may not include every nonblank cell.
Blank responses: Empty cells are not counted as a named option. Use COUNTBLANK when the number of unanswered rows is what you need.
Formula separators or quotes: Use straight quotation marks around text. Some spreadsheet locales use semicolons rather than commas between formula arguments; if Sheets flags a formula error, check the spreadsheet’s locale and the separator it expects.

For a quick audit, compare the nonblank-response total with the sum of all category counts. If they differ, look for an omitted option, an invalid value, or a range mismatch. The comparison is meaningful only when the categories cover all intended responses and each response should belong to exactly one category.

Choose a counting method for the question

COUNTIF is the simplest choice when you need one category total. A small table of COUNTIF formulas is clearer when you need a separate total for every dropdown option. COUNTIFS is appropriate when each count must also satisfy conditions in other columns. COUNTA answers how many cells are filled, while COUNTBLANK answers how many are empty; neither tells you how many cells contain a particular permitted choice.

These formulas also work in ordinary Google Sheets data ranges and do not require a special dropdown-reporting feature. In Excel, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK are available too, although the interface for creating dropdowns is different. In Google Sheets, the relevant browser menu paths are Insert > Dropdown and Data > Data validation; Excel uses data-validation controls on its ribbon instead. The formulas themselves are familiar across both programs.

If your goal is a visual report, keep the summary counts in cells and build a chart from the summary table. The chart then reflects the formula results as the dropdown selections change. For a short list of choices, a table is often easiest to audit; for a larger tracker, a chart can make changing proportions more visible, but it does not replace checking that the labels and ranges are correct.

Frequently Asked Questions

Can Google Sheets count dropdown chips?
Yes. A chip is a display style for a dropdown value. COUNTIF and COUNTIFS read the value in the cell, so the chip appearance does not require a different formula.

How do I count each dropdown option automatically?
Put each option beside a COUNTIF formula and use the option cell as the criterion, such as =COUNTIF($B$2:$B$100,D2). Fill the formula down for the remaining options. The formula updates when responses change.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF’s text matching is case-insensitive. It can still fail to match if the text differs in spelling, punctuation, or spacing.

Can I count only dropdown responses for one team?
Yes. Use COUNTIFS with one condition for the team column and another for the dropdown response column. Make sure the criteria ranges cover corresponding rows.

Why does the total of my dropdown counts not match the number of filled cells?
There may be a response value missing from your summary list, an invalid value entered under a warning-based rule, or a mismatch in the ranges. Compare the formulas’ ranges and inspect uncounted nonblank cells.

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.