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

YouTube player


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.

Counting unique values with case sensitivity
Case Sensitive Counting

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.

The PROPER Function Converting Case
Using PROPER To Convert Case


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.

ARRAYFORMULA writing results
ARRAYFORMULA Writing Results

Putting it All Together

Counting unique values regardless of capitalization
Case Insensitive Counting

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