# Counting Unique Values in Filtered List That Meet External Criteria

#### Steve Bogdanoff

##### New Member
There have been numerous posts about how to count the number of unique values existing in a specific column of a filtered table. Responses often point to array formulas that use a combination of the FREQUENCY, SUBTOTAL and MATCH functions, for example:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(\$A10,ROW(\$A\$10:\$A\$1000)-ROW(\$A10),,1)),IF(\$A\$10:\$A\$1000<>"",MATCH("~"&\$A\$10:\$A\$1000\$A\$10:\$A\$1000&"",0))),ROW(\$A\$10:\$A\$1000)-ROW(\$A10)+1),1))}

While those formulas produce accurate results in that simple situation, they do not address this slightly more advanced scenario.

Assume a filtered table with no limit on the number of active filter selections. Once filtered, the table will produce a specific number of “visible rows.” Within those visible rows there will be a specific number of unique values in a targeted column. So, for example, assume a table with a data range of A10:C1000 where column A contains region names, column B contains product names, and column C contains sales revenues. If the table is filtered on column C for sales revenues greater than \$1,000, only those rows with a value in column C that is greater than \$1,000 will be visible. From there we can use the standard array formula above to determine how many unique region names are currently visible in column A.

Now assume that cell A1 contains a specific product name, e.g., “shirts.” Here is the question: what array formula could be placed in cell A2 that would count the number of unique region names in the filtered table that ALSO have “shirts” in column B? The array formula in question would thus have to have the capacity to count unique values in column A but also evaluate them with respect to the additional criteria, in this case a column C value equal to “shirts" which is noted in a cell external to the table, A1.

An easy solution would be to simply activate a filter on column ‘B’ for “shirts.” However, that only produces a result for that one type of product. The task is to create a report that shows the number of unique regions in the filtered table for each of the possible product names: “shirts,” “pants,” “socks,” “coats,” etc. That would require an array formula for each type of product name.

With all this in mind, an important question is whether a better solution would be a VBA user-defined function?

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### mumps

##### Well-known Member
I'm not sure about a formula but a macro may do what you want. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

#### mumps

##### Well-known Member
Try:
Code:
``````Sub CountUniqueVals()
Application.ScreenUpdating = False
Dim LastRow As Long, RngList As Object, regions As Long, rng As Range, item As Range
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
regions = Range("A:A").Find("Regions", LookIn:=xlValues, lookat:=xlWhole).Row
Set RngList = CreateObject("Scripting.Dictionary")
For Each rng In Range("A12", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
If Not RngList.Exists(rng.Value) Then
End If
Next
Range("B3") = RngList.Count
RngList.RemoveAll
For Each rng In Range("A5:A" & regions - 3)
Range("A11:C" & LastRow).AutoFilter Field:=2, Criteria1:=rng
For Each item In Range("A12", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
If Not RngList.Exists(item.Value) Then
End If
Next item
rng.Offset(0, 1) = RngList.Count
RngList.RemoveAll
Next rng
Range("A11").AutoFilter
Application.ScreenUpdating = True
End Sub``````

#### Steve Bogdanoff

##### New Member
Wow - very impressive. Thank you!
Here's the practical follow-up: can that code be turned into a UDF (e.g., CountUniqueVals() ) for use in a formula in each of the four cells B5:B8?
The formula in cell B5 might look like this =CountUniqueVals(A5) and would produce the result of 2.
Your code might necessitate additional arguments than just the product value in A5 but that is the way my mind is working right now ...
Possible?
And many, many thanks for taking the time to think this through!

#### Fluff

##### MrExcel MVP, Moderator
If you want a formula try
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(\$A12,ROW(\$A\$12:\$A\$1000)-ROW(\$A12),,1)),IF((\$A\$12:\$A\$1000<>"")*(\$B\$12:\$B\$1000=A5),MATCH("~"&\$A\$12:\$A\$1000,\$A\$12:\$A\$1000&"",0))),ROW(\$A\$12:\$A\$1000)-ROW(\$A12)+1),1))

#### mumps

##### Well-known Member
You are very welcome. Perhaps Fluff's suggestion will do what you want.

#### Steve Bogdanoff

##### New Member
OUTSTANDING! Thanks for that great thinking. It was sticking that Boolean logic -- (no blank cells)*(product = what's in column A) -- structure into the main body of the formula that was throwing me for a loop.
This formula will be used to help a state department of education figure out which public school districts to support with extra help so, in theory, a whole lot of children may benefit from this great thinking.
MUCH APPRECIATED, ALL AROUND!!!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

1,095,176
Messages
5,442,835
Members
405,201
Latest member
kashyap44

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...