Ishrak Khan
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.
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.
Steps:
- To find the unique products in our retail sales dataset, we will first select cell B15.
- Next, we will use the following formula to list all unique products.
- In order to count how many times each unique product has been sold, we will then select cell C15.
- 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.
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)))}
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 {}.
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, …])
Here is a simple example of using the COUNTUNIQUE function to count the total number of unique customers.
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.
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 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.
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.
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.
Steps:
- Once again, we will select cell C15 and use the following formula.
=COUNTUNIQUEIFS($B$5:$B$11,$C$5:$C$11,"=Fruit")
- By using the fill handle feature in Google Sheets, we can quickly and easily find the final result.
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.
