Count unique values accross multiple columns that will work when filtered

rorym

New Member
Joined
Oct 24, 2011
Messages
5
I have 3 columns of data. The first column displays the store that placed an order for a mail distributed ad. The third column displays the ZIP code where the ad will be distributed. The second column lists any store that is located in that ZIP, yet did not actually place the order, but will benefit from its distribution. I need a formula that will count the combined unique store numbers in columns A and B. The formula needs to work if there are blanks, and it needs to subtotal the results if I filter any part of the table.

<TABLE style="WIDTH: 271pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=362><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" span=2 width=107><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #538ed5; WIDTH: 80pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4728 height=22 width=107>Store #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4728 width=107>Shared</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #538ed5; WIDTH: 111pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4728 width=148>ZIP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>C002081</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44035</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>C002081</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44035</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>C002081</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44035</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>C002081</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44035</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>C002081</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44039</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>H760013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44129</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>H760013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729>H950035</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44144</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729 height=20>H760013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4729> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl4730>44134</TD></TR></TBODY></TABLE>

This array formula works great for when I am only counting unique values in a single filtered column:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A4:A286,ROW(A4:A286)-MIN(ROW(A4:A286)),0,1)),MATCH(A4:A286,A4:A286,0)),IF(SUBTOTAL(3,OFFSET(A4:A286,ROW(A4:A286)-MIN(ROW(A4:A286)),0,1)),MATCH(A4:A286,A4:A286,0)))>0,1,0))

I just can't find the solution for counting unique values accross multiple columns with filters applied. Trying to avoid VBA.

Thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The result in the example I provided should be 3: there are 3 unique store numbers accross column A and B.

Thank you.
 
Upvote 0
Based on the closed post, this is what i understood,

See if it helps,


Excel Workbook
ABCDE
1tore #Shared*Count3
2C002081**C002081*
3C002081**H760013*
4C002081**H950035*
5C002081****
6C002081H950035***
7H760013****
8H760013****
9H760013****
10H950035****
11H950035****
Sheet1
 
Upvote 0
Thank you, although I need the formula(s) to account for the store in the "Shared" column. This was part of the corrections I made in my new post.
 
Upvote 0
Here's a rather complex, resource intensive formula solution. First define (Insert > Name > Define) the following names...

Name: Array1

Refers to:

=ROW(INDIRECT("1:"&ROWS(Sheet1!$A$4:$B$286)*COLUMNS(Sheet1!$A$4:$B$286)))-1

Name: Array2

Refers to:

=MOD(Array1,ROWS(Sheet1!$A$4:$B$286))

Name: Array3

Refers to:

=INT((Array1)/ROWS(Sheet1!$A$4:$B$286))

Name: Array4

Refers to:

=SUBTOTAL(3,OFFSET(Sheet1!$A$4:$B$286,Array2,Array3,1,1))

Name: Array5

Refers to:

=T(OFFSET(Sheet1!$A$4:$B$286,Array2,Array3,1,1))

Change the sheet name, accordingly. Then, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(Array4,MATCH(Array5,Array5,0)),IF(Array4,MATCH(Array5,Array5,0)))>0,1))
 
Upvote 0
Here's a custom function instead. First, copy/paste the following code in a regular module (Alt+F11 > Insert > Module > copy/paste > Alt+Q)...

Code:
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Function[/color] MyCount(rRng [color=darkblue]As[/color] Range) As Long
    [color=darkblue]Dim[/color] oDict [color=darkblue]As[/color] Dictionary
    [color=darkblue]Dim[/color] rCell [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] oDict = CreateObject("Scripting.Dictionary")
    [color=darkblue]For[/color] [color=darkblue]Each[/color] rCell [color=darkblue]In[/color] rRng
        [color=darkblue]If[/color] rCell.Value <> "" [color=darkblue]Then[/color]
            [color=darkblue]If[/color] rCell.EntireRow.Hidden = [color=darkblue]False[/color] [color=darkblue]Then[/color]
                [color=darkblue]If[/color] oDict.Exists(rCell.Value) [color=darkblue]Then[/color]
                    [color=green]'Do nothing[/color]
                [color=darkblue]Else[/color]
                    oDict.Add rCell.Value, rCell.Value
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] rCell
    MyCount = oDict.Count
[color=darkblue]End[/color] [color=darkblue]Function[/color]
[/font]

Then the custom function can be used in a worksheet as follows...

=MyCount(A4:B286)
 
Upvote 0

Forum statistics

Threads
1,217,763
Messages
6,138,459
Members
450,139
Latest member
myexcel202424

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top