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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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
20,329
Office Version
  1. 365
Platform
  1. Windows
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
20,329
Office Version
  1. 365
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,179
Messages
5,857,797
Members
431,898
Latest member
JockeyDot

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