Google Sheets: Count Cells With a Specific Dropdown Value

To count cells that contain a particular Google Sheets dropdown choice, use COUNTIF. For example, if dropdowns are in B2:B100, =COUNTIF(B2:B100,"Approved") counts cells whose value is Approved; replace the range and label with your own.

Why COUNTIF works with dropdowns

A dropdown is a way to control what people can enter in a cell, not a separate kind of data that needs a special counting function. When someone chooses an option, Sheets stores that option as the cell’s value. The cell may show the value as a colored chip, but the chip’s appearance does not change how a normal formula counts it.

COUNTIF checks a range against one condition and returns the number of cells that meet it. That makes it a good fit for questions such as “How many requests are Open?” or “How many orders are Shipped?” It updates when a dropdown selection changes, as long as the formula’s range includes that cell.

The formula counts matching cell values, not the number of times an option appears in the dropdown menu. If “Approved” is one of the available choices but nobody has selected it, the count is zero. If a cell contains a value that is not currently offered by the dropdown, the count depends on what is actually in the cell—not just on the dropdown’s configured choices.

Write a basic COUNTIF formula

The general pattern is COUNTIF(range, criterion). The range is the group of cells to inspect; the criterion is the value to count. Text criteria go inside straight double quotation marks. A common arrangement is a heading row followed by the dropdown entries, so the formula starts at row 2 rather than including the heading.

Assume B2:B100 contains status dropdowns, and 12 of those cells currently show Approved. With dropdown entries in B2:B100, this formula counts that label and returns 12:

=COUNTIF(B2:B100,"Approved")

Enter the formula in an empty cell outside the range being counted, such as D2. If you put the result inside B2:B100, that cell becomes part of the count range and could affect the result. If your data uses a different column or starts on another row, change the range accordingly.

Check the dropdown setup and values

If you are unsure which labels a range allows, select a dropdown cell and open Data > Data validation. The data-validation panel shows the rule and its choices; a dropdown can also be created from a range of cells. The menu item Insert > Dropdown is another way to create a dropdown. These settings help you check spelling and the source of the choices, but they are not needed for COUNTIF itself.

For a reliable count, make sure the formula looks at the cells containing the selections, not the list of allowed options. For example, if the choices are listed in F2:F5 but staff make selections in B2:B100, count B2:B100. Counting F2:F5 would tell you how many choice labels are listed, not how many records have each status.

Use a range that includes all current and expected entries.
Leave out header cells unless you intentionally want to count a header that matches the criterion.
Check that the criterion matches the dropdown label, including punctuation and spacing.

Use a cell as the criterion

You do not have to type the label into the formula. Point the criterion to a cell instead, which is useful when people need to change the value being counted without editing a formula. For example, put Approved in D2 and refer to D2 as the criterion. This also makes it easy to create a small summary table with one label and one count per row.

With dropdown selections in B2:B100 and the label to count in D2, enter this formula in E2. If D2 contains Approved and 12 matching entries are in the range, the result is 12:

=COUNTIF(B2:B100,D2)

Copy the formula down beside other labels in D3, D4, and so on. The data range stays the same while the criterion reference changes by row. If the labels are maintained in a separate source range, this setup can keep the summary aligned with that list.

Count more than one dropdown option

A basic COUNTIF counts one criterion at a time. To get a combined total for several different choices, add separate COUNTIF results. Suppose B2:B100 contains a single-choice status dropdown, with 9 Open entries and 8 In progress entries. With those values in B2:B100, this formula adds the two counts and returns 17:

=COUNTIF(B2:B100,"Open")+COUNTIF(B2:B100,"In progress")

This method is clear when the group of choices is short. It counts cells matching either label once each; with a single-choice dropdown, a cell cannot simultaneously be Open and In progress. For a longer list of categories, use a summary table with each label in its own cell and a copied COUNTIF formula beside it, rather than maintaining a long expression by hand.

Be precise about what “count several options” means. Adding counts for Open and In progress gives the total number of cells in either of those states. It does not count all nonblank dropdown cells, and it does not count the number of choices configured in the dropdown.

Count a dropdown value with another condition

Sometimes the question is not just how many records have a status, but how many records have that status in a particular group. COUNTIFS applies multiple conditions, such as status and region, to corresponding rows. All criteria ranges should cover the same rows so that each condition is evaluated against the same record.

Assume region names are in A2:A100 and status dropdowns are in B2:B100. If six rows have West in column A and Complete in column B, this formula returns 6:

=COUNTIFS(A2:A100,"West",B2:B100,"Complete")

This is useful for dashboards and operational summaries, such as counting completed jobs by team or pending requests by location. You can also use cell references for one or both criteria, just as with COUNTIF, so a selected region or status can drive the result.

Count blank dropdown cells

Dropdowns do not necessarily have to be filled in. To find out how many cells in the input range are empty, use COUNTBLANK. This can help identify incomplete records, but distinguish an empty cell from an explicit dropdown option such as “Not started.” The latter is a text value, so it is not blank.

With dropdown entries in B2:B100, if eight cells are empty, this formula returns 8:

=COUNTBLANK(B2:B100)

A blank count is only meaningful if the range contains the rows you intend to monitor. If the range includes many unused rows below the data, the formula will count those too. Set a practical boundary or use a range that matches the table’s actual records, then extend it as needed.

Common reasons a count looks wrong

When the result is unexpected, check the data and formula before rebuilding the dropdown. The most common problems are a range that misses some entries, a criterion with a typo, or a label that looks identical but contains an extra space or different punctuation. A heading included in the range can also affect a count if its text matches the criterion.

Wrong range: Confirm that the formula covers the column and rows where selections are made, rather than the list of dropdown options.
Different text: Compare the criterion with the actual selected value. “In progress” and “In Progress” look similar; spaces and punctuation can also differ.
Unexpected entries: Data validation controls entry, but it is worth checking for pasted or otherwise existing values that do not match the intended choices.
Wildcards: In COUNTIF criteria, an asterisk can match any sequence of characters and a question mark can match one character. If a dropdown label contains these symbols, the criterion may act as a pattern rather than a literal label.
Formula placement: Keep the result cell outside the range being counted to avoid including the formula’s own cell.

COUNTIF is not case-sensitive, so a capitalization difference alone generally will not make otherwise matching text count separately. If you need to diagnose a difficult mismatch, click the cell and inspect the value shown in the formula bar, then compare it with the criterion and the validation choices. A temporary test in a nearby cell can also help confirm that the formula is looking at the intended range.

What changes with multiple selections?

Some Google Sheets dropdowns can be configured to allow multiple selections. That changes the counting question: a cell may represent more than one selected choice rather than one standalone label. An exact criterion such as Approved is dependable for cells whose complete value is Approved, but it may not match a cell that contains Approved alongside another selection.

Before choosing a formula for multi-select data, inspect how the combined selections are represented in your sheet and decide whether you want to count cells containing a choice or count each selected choice occurrence. A wildcard criterion can find text containing a word, but it can also match unintended labels—for example, searching for “Open” could match a longer phrase containing that word. For accurate reporting, consider keeping one choice per cell or organizing selections in separate columns when the data will need frequent analysis.

Make a count summary easier to maintain

A tidy summary separates the source data from the reporting area. Keep the dropdown entries in one column, put the option labels to count in another, and place formulas beside them. Use clear headings such as Status and Count, and avoid including the heading row in the source range. This makes it easier to spot a misspelled label or a range that needs expanding.

For a small sheet, a fixed range such as B2:B100 may be sufficient. For a growing log, set the range generously or update it as new rows are added, while taking care not to include unrelated cells. If a count supports a decision or a published report, verify a few records manually and confirm that blank rows and multi-select entries are handled as intended.

Frequently Asked Questions

Can Google Sheets count a colored dropdown chip?
COUNTIF counts the cell’s value, not its chip color. If the label Approved is assigned a green chip, counting Approved counts that value regardless of the chip’s appearance.

Does COUNTIF update when someone changes a dropdown?
Yes. When a selection in the referenced range changes, Sheets recalculates the count. Make sure the changed cell is inside the range used by the formula.

Can I count a dropdown choice from another tab?
Yes. Use a sheet-qualified range, such as Tasks!B2:B100, as the range argument. If the tab name contains spaces, enclose the name in single quotation marks before the exclamation mark.

Can I count a value that is not in the dropdown list?
Yes, if that value is present in a cell and your formula’s criterion matches it. The formula counts cell contents; it does not restrict its count to the current validation options.

Why does COUNTIF return zero for a choice I can see?
Check that the formula references the cell containing the choice, that the range includes its row, and that the criterion matches the stored text. Also check for a combined multi-selection value or an unintended space or punctuation difference.

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.