Dataset Overview
In our dataset, we have information on the Quantity and Sales of various Product Categories, along with Order Dates and corresponding States. Our task is to apply filters to specific columns and then count the unique values within those filtered columns.
Adding Filters
 Select the entire dataset where you want to apply filters.
 Go to the Sort & Filter ribbon in the Data tab.
 Click on the Filter tool to add filters to your dataset.
You’ll see a dropdown arrow next to each column header. Use these arrows to easily filter the data based on your criteria.
Method 1 – Using an Array Formula
This method involves an array formula that may seem a bit complex but is quite handy. Suppose you want to filter the dataset based on Texas states.
Follow these steps:
 Click the dropdown arrow next to the States column header.
 Check the box for Texas to keep only the data related to that state.
 After applying the filter, you’ll have a filtered dataset.
 To count the unique values in the Product Category column, insert the following array formula in cell B18:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C15)ROW(C5),,1)), IF(C5:C15>"",MATCH("~"&C5:C15,C5:C15&"",0))),ROW(C5:C15)ROW(C5)+1),1))
 In this formula:
 C5 represents the starting cell of the Product Category column.
 C5:C15 is the cell range for that column.
Formula Breakdown:
In this formula, several functions work together to achieve the desired result. Let’s break it down step by step:
 MATCH Function:
 The MATCH function searches for specific values within a range and returns their relative positions.
 In this case, it finds the values in the C5:C15 cell range (which represents the “Product Category” column) and returns an array of relative positions: {1,2,3,4,5,6,7,8,9,10,11}.
 ROW Function:
 The ROW function returns the row number of a given cell.
 It’s used here to determine the row numbers within the C5:C15 range.
 OFFSET Function:
 The OFFSET function returns a reference to a cell or range based on a specified offset from a given starting point.
 In this formula, it returns a reference to the cell specified by C5 (the starting cell of the “Product Category” column).
 SUBTOTAL Function:
 The SUBTOTAL function calculates a subtotal for a range of cells, considering only visible rows (i.e., rows that are not filtered out).
 It’s used here to count the visible rows within the filtered dataset.
 FREQUENCY Function:
 SUM Function:
 The SUM function aggregates the values returned by the FREQUENCY function.
 It gives us the total count of unique values in the specified cell range.
Output
When you press ENTER after entering this formula, you’ll get the following output:
 The number of unique values is 4 because the two product categories (Vegetable and Fruit) have duplicates.
Note:
If you’re not using Microsoft 365, remember to press CTRL + SHIFT + ENTER when dealing with an array formula.
Method 2 – Using the COUNTIF Function
 Filtering the Dataset:
 Before applying the COUNTIF function, let’s assume you want to filter the dataset based on the date 20Apr2022. Follow the same steps as in the first method to apply the filter.
 Applying the COUNTIF Function:
 After filtering the entire dataset, you can use the COUNTIF function to count unique values within the filtered column.
 The formula to achieve this is:
=(COUNTIF($C$5:$C6,$C6)<2)*1

 Here:
 $C$5:$C6 represents the cell range of the Product Category column.
 $C6 is the starting cell of the filtered field.
 The COUNTIF function returns 1 for unique values and 0 for duplicate values.
 Here:
 Handling Errors:
 If you closely examine the screenshot, you’ll notice an error in cell E12, where Vegetable shows 0 even though it is a unique category.

 To address this, add a helper column to check visible rows using the SUBTOTAL function:
=1*SUBTOTAL(3,$D6)
Here, $D6 is the output found using the COUNTIF function and multiplying by 1 helps avoid issues with the last row in the filter range.
 Displaying Visible Rows:
 Use the IF logical function to display visible rows:
=IF($E6,$C6,"")


 If $E6 (the starting cell of the output from the previous step) is true, it shows the corresponding value from column C; otherwise, it displays a hyphen (“–”) for hidden rows.

 Accurate Unique Values:
 To obtain accurate unique values (excluding hidden rows), enter the following formula:
=($F6<>"")*(COUNTIF($F$6:$F6,$F6)<2)


 Here, $F6 is the starting cell of the output obtained earlier.

 Final COUNTIF Formula:
 To count the unique values (excluding zero values), insert the following formula in cell B18:
=COUNT(IF(G6:G15<>0, G6:G15))


 Here, G5:G15 represents the cell range of the Accurate Unique Values.

Method 3 – Combined Use of COUNTA, UNIQUE and FILTER Functions
 Counting Visible Rows:
 Before applying the combined formula, start by counting the visible rows using the SUBTOTAL function:
=SUBTOTAL(3,C6)


 Here, 3 corresponds to COUNTA, which counts cells including hidden rows, and C6 represents the starting cell of the filtered column.

 Combined Formula:
 Insert the following combined formula to achieve our goal:
=COUNTA(UNIQUE(FILTER(C6:C15,G6:G15)))


 In this formula:
 C6:C15 represents the cell range of the Product Category column.
 G6:G15 represents the cell range of the Visible Rows (filtered dataset).
 In this formula:

 Function Breakdown:
Method 4 – Counting Unique Values Using the Advanced Filter
 Filtering Data Based on Quantity:
 Suppose you want to filter data where the quantity is greater than or equal to 30 (cell range F4:F6).
 Creating a New Working Sheet:
 To obtain the filtered unique data in a new working sheet, follow these steps:
 Create a new sheet.
 Go to the Sort & Filter ribbon in the Data tab.
 Choose the Advanced tool.
 To obtain the filtered unique data in a new working sheet, follow these steps:
 Setting Up the Advanced Filter:
 While keeping the cursor over the new working sheet (named Advanced Filter2):
 Specify the List range as Advanced Filter1′!$B$4:$D$15.
 Set the Criteria range as Advanced Filter1′!$F$4:$F$5.
 Before proceeding, check the circle next to the Copy to another location option.
 While keeping the cursor over the new working sheet (named Advanced Filter2):


 Note: Advanced Filter1 refers to the existing working sheet, and Advanced Filter2 is the name of the new working sheet.

 Copying Unique Records:
 Specify Advanced FIlter2′!$B$4 after the Copy to option.
 Check the box for Unique records only.
 Press OK.
 Result:
 After completing these steps, you’ll have a filtered dataset containing unique records.
 Counting Unique Values:
 To count the unique values, simply use the ROWS function with the following formula:
=ROWS(B5:B10)


 Here, B5:B10 represents the cell range for the filtered Product Category.

Method 5 – Using the Pivot Table to Count Unique Values in Filtered Column
In addition to the four previously mentioned methods, you can utilize the Pivot Table—a powerful feature in Excel—to efficiently analyze larger datasets. Creating a Pivot Table is straightforward:
 Choose the entire dataset you want to analyze.
 Go to the Insert tab and select Pivot Table from the options.
 Check the box for New Worksheet to create the Pivot Table on a new sheet.
 Optionally, select Add this data to the Data Model.
 Place the Product Category field in the Rows area.
 Put the Sales field in the Values area.
 Rightclick on a cell in Column B.
 Choose Value Field Settings.
 Select Distinct Count as shown in the image below.
 The output will display the number of unique values (in this case, 6).
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C15)ROW(C5),,1)), IF(C5:C15>””,MATCH(“~”&C5:C15,C5:C15&””,0))),ROW(C5:C15)ROW(C5)+1),1))
Above function working file for text. I tried this on Dates but the function is not working
In this situation, you can use an easy alternative. Try using:
=COUNTA(UNIQUE(date range))
The above formula can easily count the unique date values.
Hi I found this site on a search. My spreadsheet uses the following formula:
COUNTA(UNIQUE(FILTER(INDIRECT(Dynamic Range),(Criteria1=Value1)*(Criteria2=Value2),””)))
It is essentially the same formula you use aside fom using indirect ranges and an “if empty” condition for the FILTER function. The formula works well as long as the criteria have at least 1 match. If there are no matches, FILTER returns a blank and is counted by COUNTA so the result is 1 instead of 0. Removing the “is empty” condition doesn’t change the result. The problem is that excel will not let me change the COUNTA function to COUNTIFS in the formula which would allow me to exclude the blank entry.
Have you been able to avoid this problem?
Thanks for this! It saved me a lot of work…
Dear Joris,
You are most welcome. We are glad to hear that.
Regards
ExcelDemy