✦Template Printable Ideas Insights

Mastering COUNTIF with Drop-Down Lists in Google Sheets

By

The COUNTIF function in Google Sheets is a powerful tool that lets you count the number of cells that meet a certain criteria. But what happens when that criteria comes from a dropdown list? In this guide, we'll walk you through the process of using COUNTIF with a dropdown list in Google Sheets.

To start, let's assume you have a dropdown list in cell A2 with options "Apple", "Banana", "Cherry". In cells B2:B10, you've listed these fruits multiple times. Now, you want to count how many times each fruit appears using the COUNTIF function.

Using COUNTIF with a Single Dropdown List

The basic syntax of the COUNTIF function is COUNTIF(range, criteria). In our case, the range is B2:B10 and the criteria is the fruit we want to count, which comes from the dropdown list.

Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Google Sheets Data Validation – How to create dependent drop down list in Google Sheets
Google Sheets Data Validation – How to create dependent drop down list in Google Sheets
How to Add a Drop Down List in Google Sheets (Step-by-Step)
How to Add a Drop Down List in Google Sheets (Step-by-Step)

In cell C2, enter the following formula: `=COUNTIF(B$2:B$10, $A2)`. Here's what this does:

  • `B$2:B$10` is the range of cells we want to count.
  • `$A2` is the cell with the dropdown list. The dollar signs keep the cell reference absolute, so when you drag the formula down, it always pulls from the same dropdown list.

Now, in cell C2, you'll see the count of times "Apple" appears in B2:B10. To count the other fruits, simply drag the formula down, and the dropdown list will update automatically.

How to Add a Drop-Down List in Google Sheets and Excel - Make Tech Easier
How to Add a Drop-Down List in Google Sheets and Excel - Make Tech Easier
Google Sheets Drop Down Lists: Complete Guide
Google Sheets Drop Down Lists: Complete Guide
Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function

Counting Occurrences of Non-Existent Options

What if you want to count occurrences of fruits that don't exist in the list? For example, you might want to count the number of "Dragon Fruits", even if none exist in your data.

The COUNTIF function will return a 0 for non-existent options. To display a 0 or "No data" for these, you can wrap your COUNTIF function in an IFERROR function. In cell C2, enter `=IFERROR(COUNTIF(B$2:B$10, $A2), "No data")`. This will display "No data" for fruits that don't appear in the range, and the count for fruits that do.

Using COUNTIF with a Multi-Select Dropdown List

What if your dropdown list allows for multiple selections, and you want to count the occurrences of each option? This gets a bit trickier, but it's still possible with a little bit of help from the REGEXMATCH function.

How to Create a Date Countdown in Google Sheets, Infographic
How to Create a Date Countdown in Google Sheets, Infographic
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Contact list template for Google Sheets with built in search
Contact list template for Google Sheets with built in search
15+ Ways for Teachers to Use Google Sheets in the Classroom - SULS0187
15+ Ways for Teachers to Use Google Sheets in the Classroom - SULS0187

The REGEXMATCH function checks if a text string matches a regex pattern. In our case, we can use it to check if the fruit in each cell contains the fruit we're looking for.

In cell C2, enter `=ArrayFormula(COUNTIF(REGEXMATCH(B2:B10, A2), TRUE))`. Here's how this works:

  • `REGEXMATCH(B2:B10, A2)` checks if each fruit in B2:B10 contains the fruit in A2. It returns an array of TRUE/FALSE values.
  • `COUNTIF(REGEXMATCH(B2:B10, A2), TRUE)` counts the number of TRUE values in the array, which corresponds to the number of times the fruit appears.
  • `ArrayFormula()` automatically adjusts the formula to apply to each item in the range, so you can see the counts for each fruit.

Now, regardless of the number of fruits selected in the dropdown list, this formula will count their occurrences in your data.

Google Sheets Weekly Schedule Template (Free Download)
Google Sheets Weekly Schedule Template (Free Download)
the google sheets are in green and white
the google sheets are in green and white
Google Sheets Checklists
Google Sheets Checklists

Using Real-World Data

To make this more practical, let's say you have a dataset of orders, and you want to count the number of orders for each fruit in your shopping list (which you've turned into a dropdown list). In this case, the range would be the column containing the fruit names, and the criteria would be the fruit in the dropdown list.

Using the `COUNTIF()` function in conjunction with dropdown lists in Google Sheets can save you a significant amount of time, especially in real-world data analysis scenarios.

Remember, the key to any good data analysis is knowing your data. Spend time understanding what your numbers mean, and always double-check your formulas to ensure they're working as expected.

As you delve deeper into the world of Google Sheets functions and dropdown lists, you'll find an array of possibilities to streamline your work and make data-driven decisions. So, happy counting!