How to Count Data Validation in Google Sheets

Using data validation dropdowns in Google Sheets is one of the best ways to keep your data consistent and easy to manage. But once you have that structured data, you need to be able to analyze it. This article will walk you through exactly how to count selections from your data validation lists, using a few powerful but simple functions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Data Validation? A Quick Refresher

Before we start counting, let's quickly clarify what we're working with. In Google Sheets, data validation is a feature that restricts the type of data that can be entered into a cell. The most common use case, and the one we'll focus on, is creating a dropdown list of predefined options.

For example, you might create a project tracker and use a data validation dropdown in your "Status" column with three options: "Not Started", "In Progress", and "Done". This ensures everyone uses the same terms, which makes reporting on that data reliable and straightforward.

You can create one by selecting a cell or range, going to Data > Data validation, and adding your list of items. Now, instead of typing freely, users select an option from a tidy list.

Counting a Specific Dropdown Selection with COUNTIF

The most common task is counting how many times a specific option has been selected. For instance, in our project tracker, you'll want to know, "How many tasks are currently 'In Progress'?" The perfect tool for this is the COUNTIF function.

The COUNTIF function counts cells within a range that meet a single specific condition.

Its syntax is simple:

=COUNTIF(range, criterion)

• range: The group of cells you want to count (e.g., your "Status" column).
• criterion: The condition you want to count for (e.g., the text "In Progress").

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Example: Counting "In Progress" Tasks

Let's say your task statuses are in column C, from cell C2 to C50.

• Click an empty cell where you want to see the total.
• Type the following formula and press Enter:

=COUNTIF(C2:C50, "In Progress")

And that’s it! Google Sheets will instantly display the total number of cells in that range containing the exact text "In Progress." You can use the same logic to count "Done" tasks or any other option from your dropdown list.

Pro Tip: Use a Cell Reference for the Criterion

Hard-coding your criteria ("In Progress") directly into the formula works, but it’s not very flexible. A better approach is to reference another cell.

For example, type "In Progress" in cell E1. Then, you can adjust your formula like this:

=COUNTIF(C2:C50, E1)

Now, if you want to count "Done" tasks, you just have to change the text in cell E1 to "Done" — no need to edit the formula at all. This is incredibly useful for building small, interactive dashboards within your sheets.

Counting with Multiple Conditions: The COUNTIFS Function

What if you need to be more specific? For instance, maybe you want to count the number of tasks assigned to a particular team member that are also "Done." When you have two or more conditions, you need the COUNTIFS function — the plural version of COUNTIF.

The COUNTIFS function checks multiple ranges against multiple criteria and only counts rows where all conditions are met.

The syntax is:

=COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2, ...)

• criteria_range1: The first range to check (e.g., the "Assigned To" column).
• criterion1: The condition for the first range (e.g., "Alice").
• criteria_range2: The second range to check (e.g., the "Status" column).
• criterion2: The condition for the second range (e.g., "Done").

Example: Counting "Done" Tasks Assigned to Alice

Imagine your task assignments are in column B ("Assigned To") and statuses are in column C ("Status"). You want to know how many tasks Alice has completed.

• Find an empty cell for your running total.
• Enter this formula:

=COUNTIFS(B2:B50, "Alice", C2:C50, "Done")

This formula tells Google Sheets to look through rows 2 to 50 and count only the rows where the value in column B is "Alice" and the value in column C is "Done."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Counting All Filled-In Dropdowns (Non-Empty Cells)

Sometimes you don't care what the answer is, only that an answer was given. For example, if you sent out a survey via a spreadsheet, you might want to know how many people have responded by filling out a dropdown, regardless of their selection.

For this job, the COUNTA function is your go-to. It simply counts every cell in a range that is not empty.

The syntax is as straightforward as it gets:

=COUNTA(range)

Example: Counting Total Responses

If your survey responses (from a dropdown) are in column D, from D2 to D100, you can count the total number of entries with this formula:

=COUNTA(D2:D100)

This will give you a total count of all cells that have any value in them, which is a quick way to see how much of your list is complete.

Advanced Counting with QUERY and FILTER

When you want to move beyond simple counts and create a summary report, the QUERY and FILTER functions offer much more power and flexibility. While they might look more intimidating, they are great tools to learn for anyone who spends a lot of time in Google Sheets.

Dynamically Summarize All Dropdown Options with QUERY

The QUERY function allows you to use SQL-like commands to filter and aggregate your data. Instead of writing separate COUNTIF formulas for each status, you can create a full summary table with a single formula.

Let's use it to count how many tasks exist for each status in our project tracker (Column C).

• Enter this formula:

=QUERY(C2:C50, "SELECT C, COUNT(C) WHERE C IS NOT NULL GROUP BY C")

Here’s what this code is doing:

• QUERY(C2:C50, ...) tells Sheets we want to run a query on our status column.
• "SELECT C, COUNT(C) ..." tells it to select the values in column C and also count them.
• " ... WHERE C IS NOT NULL ..." instructs it to ignore any empty cells.
• " ... GROUP BY C" is the magic part. It groups the counts by each unique value found ("Done", "In Progress", etc.).

Entering this single formula will create a neat two-column table showing each dropdown option and its corresponding count. It automatically updates as you change statuses in your data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Count Items Based on an Opposite Condition with FILTER

The FILTER function lets you create a temporary new range of data that meets certain conditions. You can then wrap this inside another function, like COUNTA, to count the results.

Let's say you want to see how many tasks are still open — meaning their status is anything except "Done."

• Enter this formula:

=COUNTA(FILTER(C2:C50, C2:C50 <> "Done"))

Here’s how it works:

1. FILTER(C2:C50, C2:C50 <> "Done") first creates a list of all cells from C2:C50 where the value is not equal to ("<>") "Done."
2. COUNTA(...) then takes that resulting list and counts how many items are in it.

This is a great technique for when counting what's left is easier than counting every other category.

Final Thoughts

By mastering functions like COUNTIF, COUNTIFS, COUNTA, and QUERY, you can turn your structured data from dropdowns into meaningful reports directly within Google Sheets. These formulas give you the ability to quickly summarize project statuses, survey responses, or any other categorized data, helping you track progress and make better decisions.

While building these reports manually is powerful, we know it's often just one part of a much bigger reporting puzzle. You end up pulling data from different apps, wrangling it in spreadsheets, and constantly updating formulas just to keep tabs on performance. With Graphed, we connect directly to all your data sources — like Google Analytics, Shopify, QuickBooks, and Salesforce — and let you build live dashboards simply by describing what you want to see. Instead of writing formulas, you can just ask, "Show me my 'Done' projects next to this month's revenue," and get an answer in seconds.