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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

rorym

New Member
Joined
Oct 24, 2011
Messages
5
The result in the example I provided should be 3: there are 3 unique store numbers accross column A and B.

Thank you.
 

snoopyhr

Active Member
Joined
Aug 12, 2002
Messages
395
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
 

rorym

New Member
Joined
Oct 24, 2011
Messages
5
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,500
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))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,500
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,975
Members
416,953
Latest member
broexc

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
Top