Use COUNTIF Function to Count Unique Values in Google Sheets

Using the COUNTIF and UNIQUE functions in Google Sheets can greatly benefit businesses and finance professionals by quickly identifying and tracking unique values within a dataset. This powerful tool allows for efficient data analysis and can save valuable time and resources. For example, a retail company could use this function to identify unique customer purchases and track sales trends.

overview image of countif unique value in google sheets

 


A Sample of Practice Spreadsheet

You can copy our spreadsheet that we’ve used to prepare this article.


2 Simple Ways to Use COUNTIF Function to Count Unique Values in Google Sheets

There are a few ways to use count unique values in Google Sheets, including using the COUNTIF and UNIQUE functions together, using an ARRAYFORMULA, or using a combination of the COUNTIF and FILTER functions.

1. Applying COUNTIF and UNIQUE Functions

We will use a retail sales dataset to find unique products and count how many times they were sold.

dataset for countif unique values in google sheets

Steps:

  • To find the unique products in our retail sales dataset, we will first select cell B15.

selecting cell in google sheets

  • Next, we will use the following formula to list all unique products.

applying unique function to countif unique values in google sheets

  • In order to count how many times each unique product has been sold, we will then select cell C15.

selection of cell in google sheets

  • Using the following formula, we will count the number of times each unique product has been sold.
=COUNTIF($B$5:$B$11,B15)
  • Finally, by pressing ENTER, we will be able to see the result.

applying countif function to count sold times in google sheets

As shown in the image, we can see that shoes were sold the most at 3 times, while pants and jackets were each sold only once.

Read More: How to Count Unique Values in Multiple Columns in Google Sheets


2. Using ARRAYFORMULA to Count Unique Value Frequency

We can also use a simple formula to get the result in one step. By selecting cell B15 first, we will use the following formula.

={UNIQUE(B5:B11),ARRAYFORMULA(COUNTIF(B5:B11,UNIQUE(B5:B11)))}

use of arrayformula function

Formula Breakdown:

  • The first part of the formula is “UNIQUE(B5:B11)“, which finds all the unique items in the range of cells from B5 to B11.
  • In the second part, the UNIQUE function finds all the unique items in the range B5:B11.
  • In the range B5:B11, the COUNTIF function counts how often each distinct item appears.
  • The ARRAYFORMULA function applies the above counting to all the unique items found by the UNIQUE function.
  • To combine the results of the two functions into one output, we used the curly braces {}.

final result of using arrayformula function to countif unique values in google sheets

In the end, we may say that the formula above yields the same outcome as it previously did. The formula shows a list of unique products and how often they were sold.

Read More: How to Get Unique Values Without Blanks in Google Sheets


How to Utilize COUNTUNIQUE Function to Count Unique Types of Data in Google Sheets

The COUNTUNIQUE function counts the number of unique values within a specified range of cells in a spreadsheet. For example, if you have a list of customers and want to know how many unique customers you have, you can use COUNTUNIQUE to get the number.

Syntax:

COUNTUNIQUE(value1, [value2, …])

syntax of countunique function

Here is a simple example of using the COUNTUNIQUE function to count the total number of unique customers.

example of countunique function in google sheets

With this function as the base, we can count all instances of data if they are unique in the dataset in Google Sheets. Here are two examples:

1. Utilizing COUNTUNIQUE and FILTER Functions

Assume that we have a similar dataset for our primary method. However, we will determine the number of unique products sold in each category this time. To resolve this, we’ll combine the COUNTUNIQUE and FILTER functions.

dataset for countunique with filter function

Steps:

  • Firstly, we will select cell C15.
  • Secondly, we will use the following formula.
=COUNTUNIQUE(FILTER($B$5:$B$11,$C$5:$C$11=B15))

formula for countunique with filter function

Formula Breakdown:

  • Here, the FILTER function filters the specified range (B5:B11) based on specified criteria (C5:C11=B15).
  • The COUNTUNIQUE function counts the number of unique items in the filtered range returned by the FILTER function.
  • With the help of the fill handle feature of google sheets, we will get the final result.

final output of utilizing countunique and filter functions in google sheets

The formula counts the number of unique products in the category of Clothing and Footwear. It shows 3 unique products under Clothing and only 1 under Footwear.

Read More: How to Filter Unique Values in Google Sheets (5 Simple Ways)


2. Implementing COUNTUNIQUEIFS Function

We can also use the COUNTUNIQUEIFS function which is relatively new in Google Sheets. Check the following steps to comprehend the use of COUNTUNIQUEIFS.

syntax of countuniqueifs function

We will use the following dataset of a grocery shop to demonstrate the use of the COUNTUNIQUEIFS function in google sheets. With its help, we will try to count the total number of unique products for each category.

dataset for implementing countuniqueifs function

Steps:

  • Once again, we will select cell C15 and use the following formula.
=COUNTUNIQUEIFS($B$5:$B$11,$C$5:$C$11,"=Fruit")

formulation of countuniqueifs function

  • By using the fill handle feature in Google Sheets, we can quickly and easily find the final result.

countuniqueifs function to countif unique value in google sheets

In sum, the COUNTUNIQUEIFS function allows you to find the unique count of values in a range based on multiple criteria. This function can be very useful for data analysis, particularly when you need to determine the number of unique values in a range that meets specific conditions.

Read More: How to Use Pivot Table to Count Unique Values in Google Sheets


Conclusion

Using the COUNTIF with other functions in Google Sheets allows businesses to count unique values easily, simplifying data analysis and decision-making. This powerful tool can help identify trends and patterns, improving efficiency and effectiveness in operations. Check OfficeWheel for more business-relevant content.


Related Articles

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo