Count unique values accross multiple columns that will work when filtered

[rorym]

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.

 

[rorym]

The result in the example I provided should be 3: there are 3 unique store numbers accross column A and B.

Thank you.

 

[snoopyhr]

Based on the closed post, this is what i understood,

See if it helps,

Excel Workbook
	A	B	C	D	E
1	tore #	Shared	*	Count	3
2	C002081	*	*	C002081	*
3	C002081	*	*	H760013	*
4	C002081	*	*	H950035	*
5	C002081	*	*	*	*
6	C002081	H950035	*	*	*
7	H760013	*	*	*	*
8	H760013	*	*	*	*
9	H760013	*	*	*	*
10	H950035	*	*	*	*
11	H950035	*	*	*	*
Sheet1

 

[rorym]

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]

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]

Here's a custom function instead. First, copy/paste the following code in a regular module (Alt+F11 > Insert > Module > copy/paste > Alt+Q)...

[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)