✦Template Printable Ideas Insights

Count Dropdown Values in Google Sheets: Step-by-Step Guide

By

Ever found yourself needing to tally the unique items in a dropdown list within Google Sheets? While Google Sheets doesn't have a built-in dropdown counter, we can achieve this with a bit of creativity and formulas. Let's dive into how to count dropdown in Google Sheets efficiently.

Understanding dropdown lists and their usage is key before attempting to count them. Dropdown lists, also known as Data Validation lists, allow you to limit data entry to a predefined list of values. To create one, you simply select a cell, click on 'Data' in the menu, then 'Data validation' and specify your list of values. Now let's explore how to count these dropdown lists.

Using the UNIQUE Function to Count Dropdown Items

The UNIQUE function, introduced in Google Sheets in 2021, is your secret weapon for counting dropdown items. This function returns a unique list of values from a range or by specifying criteria.

Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages

Here's how to use it: Assume your dropdown list starts from cell A2 and goes down to A10. In a new cell where you want your count to display (let's say B1), type '=UNIQUE(A2:A10)' and press enter. Voila! You'll see the count of unique items in your dropdown list.

Case-Insensitive Counting with UNIQUE and arranged result

By default, UNIQUE accounts for case sensitivity. To count without considering case, add the TRUE keyword at the end of your function: '=UNIQUE(A2:A10, TRUE)'. If you want the results to be sorted (alphabetically for texts or numerically for numbers), add FALSE: '=UNIQUE(A2:A10, FALSE, FALSE)'.

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)
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
📊 Color Coded Dropdowns in Google Sheets
📊 Color Coded Dropdowns in Google Sheets

Remember, UNIQUE does not count empty cells or cells containing errors. If you want to count those as well, you'll need to use another approach, like COUNTA, but with some additional steps to filter out blanks and errors.

Counting Dropdown Items with COUNTA and Filters

COUNTA is a simple yet powerful function that counts the number of cells in a range that contain a value. Let's use it along with a bit of conditional formatting to achieve our goal.

First, apply this formula: '=COUNTA(A2:A10)'. This will count both used and empty cells. Next, to filter out empty cells, you can apply a custom formula rule for conditional formatting: '=A2 <> ""'. This tells Google Sheets to apply formatting only to non-empty cells. Finally, ajust the number format to display only the count without the comma separation. Now, you have your count without including empty cells.

How to Create a Date Countdown in Google Sheets, Infographic
How to Create a Date Countdown in Google Sheets, Infographic
the 13 google sheets shortcuts list is shown in green and has white writing on it
the 13 google sheets shortcuts list is shown in green and has white writing on it
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets Attendance with drop down menu hack
Google Sheets Attendance with drop down menu hack
How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function

Including Errors in Your Count

Sometimes, you may want to include cells with errors in your count. You can use the ISERROR function in combination with ISBLANK to achieve this: '=COUNTA(ISBLANK(A2:A10), ISERROR(A2:A10))'. This will count both non-empty cells and those containing errors, but not the empty ones.

Counting dropdown items in Google Sheets might seem daunting at first, but with the right functions and a bit of tweaking, it's a breeze. So go ahead, delve into your spreadsheets, and master these counting techniques to streamline your data management. Happy counting!

6 Crazy Google Sheets Formulas You Should Know
6 Crazy Google Sheets Formulas You Should Know
Essential Google Sheets Text and Web Formulas for Productivity
Essential Google Sheets Text and Web Formulas for Productivity
Google Sheets Checklists
Google Sheets Checklists