Count Unique Values Regardless of Capitalization – Google Sheets

This tutorial teaches us how to count unique values in a spreadsheet while ignoring capitalization.

Get a copy of this live Google Spreadsheet to follow along.

If you have a list of data in Google Sheets, you can easily count the unique values with the COUNTUNIQUE function. However, this function is case-sensitive and may not provide the desired result. Let’s look at the following example to understand this limitation with COUNTUNIQUE and create our formula to count unique values regardless of capitalization.

Video Tutorial

Example

Case-Sensitive Counting

Below is the list of words to count. However, the formula =COUNTUNIQUE(A1:A5) treats all the capitalization variants as unique values. For example, Google Sheets counts Pear and pear as two unique values. We want to count Pear and pear as one unique value and the same with Peach and PEACH.

PROPER Function

We need to add two more functions to our formula to solve this. First, we use PROPER to capitalize the first letter in each word. However, =PROPER(A3) only converts one value at a time.

ARRAYFORMULA Function

You can’t embed five PROPER functions inside COUNTUNIQUE, so we need a function that repeats PROPER. ARRAYFORMULA repeats other functions, so it’s a natural fit. This function allows us to give the PROPER function an array instead of one cell at a time.

Putting it All Together

Now we arrive at =COUNTUNIQUE(ARRAYFORMULA(PROPER(A1:A5))), which is doing what we need. This combination of functions converts each value to the same case and checks for unique values.

Related Posts