Counting Unique Values in Filtered List That Meet External Criteria

[Steve Bogdanoff]

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?

 

[mumps]

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.

 

[Steve Bogdanoff]

Thank you for the instructions on sharing a file.
Here is the link to a one sheet file that I have uploaded to DropBox that I hope will clearly convey my intentions:
https://www.dropbox.com/s/6lqmftat1... List That Match Specified Criteria.xlsx?dl=0
Thanks!

 

[mumps]

Try:

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]

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]

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]

You are very welcome. Perhaps Fluff's suggestion will do what you want.

 

[Steve Bogdanoff]

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]

You're welcome & thanks for the feedback