Counting Unique Values in Filtered List That Meet External Criteria

Steve Bogdanoff

New Member
Joined
May 11, 2010
Messages
10
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?
 

Some videos you may like

Excel Facts

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,896
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
Joined
Apr 11, 2012
Messages
8,896
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
            RngList.Add rng.Value, Nothing
        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
                RngList.Add item.Value, Nothing
            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
Joined
May 11, 2010
Messages
10
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
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
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
Joined
Apr 11, 2012
Messages
8,896
You are very welcome. :) Perhaps Fluff's suggestion will do what you want.
 

Steve Bogdanoff

New Member
Joined
May 11, 2010
Messages
10
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
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top