Count Unique Values with Multiple Criteria

I have a data sheet that contains 15 columns I want to count unique
BranchNames is 1 column based upon criteria in other columns. The data sheet
can range from 5,000 to 50,000 rows and will vary each time I run my macros.

The results I am looking for is to get the count of Unique BranchNames for
MonthID = 1 so that my result is 3 (the actual number of rows where the
condition 1 is met can range from 500 to 20,000 and the result I would want
to see is anywhere from 10 to 150). The reason is that each branchname can
have multiple companies and multiple orders per company.

All the Sumproduct formula I have found return the number of rows where the
MonthID is 1, but not the unique count of the BranchName where the MonthID is
1.

MonthID BranchName Company
1 Boston XYX
1 Boston Widgets
1 New York Widgets
1 Seattle ABC Co
2 New York Widgets
2 Seattle ABC Co
2 New York Widgets
2 Boston XYX
2 Boston Widgets
3 New York Widgets
3 Seattle ABC Co
3 New York Widgets

Regards,
JohnV

Using your example with month id starting in A2 going to A13 and branch
names in B2:B13 then use

=SUM(IF(FREQUENCY(IF($A$2:$A$13=1,MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$1)+1)>0,1))

entered with ctrl + shift & enter

will return 3

expect it to be somewhat slow if the data grows large

If that's the case I would use a help column and a formula like

=COUNTIF($B$2:B2,B2)

copy down all along to the last value

then use

=SUBTOTAL(3,B2:B13)

and finally apply filter>autofilter and filter on 1 in the Month id and 1 in
the help column
then the subtotal formula would return the number of distinct branch names

It would be very easy to automate an autofilter using VBA where you could
put in the
filter criteria in input boxes, that would be faster than using an array
formula


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"JohnV" <[email protected]> wrote in message
news:[email protected]...
>I have a data sheet that contains 15 columns I want to count unique
> BranchNames is 1 column based upon criteria in other columns. The data
> sheet
> can range from 5,000 to 50,000 rows and will vary each time I run my
> macros.
>
> The results I am looking for is to get the count of Unique BranchNames for
> MonthID = 1 so that my result is 3 (the actual number of rows where the
> condition 1 is met can range from 500 to 20,000 and the result I would
> want
> to see is anywhere from 10 to 150). The reason is that each branchname can
> have multiple companies and multiple orders per company.
>
> All the Sumproduct formula I have found return the number of rows where
> the
> MonthID is 1, but not the unique count of the BranchName where the MonthID
> is
> 1.
>
> MonthID BranchName Company
> 1 Boston XYX
> 1 Boston Widgets
> 1 New York Widgets
> 1 Seattle ABC Co
> 2 New York Widgets
> 2 Seattle ABC Co
> 2 New York Widgets
> 2 Boston XYX
> 2 Boston Widgets
> 3 New York Widgets
> 3 Seattle ABC Co
> 3 New York Widgets
>
> Regards,
> JohnV

In addition to the suggestions made by Peo, assuming that A2:D13
contains the data, here are a couple of other possibilities...

1) If the data is sorted by the MonthID column, in ascending order...

Let E2 contain the MonthID

F2:

=MATCH(E2,$A$2:$A$13,0)

G2:

=MATCH(E2,$A$2:$A$13)

H2:

=SUMPRODUCT((OFFSET($B$2,F2-1,0,G2-F2+1)<>"")/COUNTIF(OFFSET($B$2,F2-1,0,
G2-F2+1),OFFSET($B$2,F2-1,0,G2-F2+1)&""))

2) Using a helper column...

D2, copied down:

=A2&"#"&B2

F2:

=SUMPRODUCT(($A$2:$A$13=E2)/COUNTIF($D$2:$D$13,$D$2:$D$13&""))

....where E2 contains the MonthID.

Hope this helps!


In article <[email protected]>,
JohnV <[email protected]> wrote:

> I have a data sheet that contains 15 columns I want to count unique
> BranchNames is 1 column based upon criteria in other columns. The data sheet
> can range from 5,000 to 50,000 rows and will vary each time I run my macros.
>
> The results I am looking for is to get the count of Unique BranchNames for
> MonthID = 1 so that my result is 3 (the actual number of rows where the
> condition 1 is met can range from 500 to 20,000 and the result I would want
> to see is anywhere from 10 to 150). The reason is that each branchname can
> have multiple companies and multiple orders per company.
>
> All the Sumproduct formula I have found return the number of rows where the
> MonthID is 1, but not the unique count of the BranchName where the MonthID is
> 1.
>
> MonthID BranchName Company
> 1 Boston XYX
> 1 Boston Widgets
> 1 New York Widgets
> 1 Seattle ABC Co
> 2 New York Widgets
> 2 Seattle ABC Co
> 2 New York Widgets
> 2 Boston XYX
> 2 Boston Widgets
> 3 New York Widgets
> 3 Seattle ABC Co
> 3 New York Widgets
>
> Regards,
> JohnV

Thank you Domenic and Peo for replies.

I guess I just have too much data for the types of analysis I want to
perform. My work around is to grab multiple datasets from my database and
then build the various reports off them. This increases the size of my
workbook template, but it greatly reduces the processing / calculation times.

Once again, thank you.
JohnV

"Domenic" wrote:

> In addition to the suggestions made by Peo, assuming that A2:D13
> contains the data, here are a couple of other possibilities...
>
> 1) If the data is sorted by the MonthID column, in ascending order...
>
> Let E2 contain the MonthID
>
> F2:
>
> =MATCH(E2,$A$2:$A$13,0)
>
> G2:
>
> =MATCH(E2,$A$2:$A$13)
>
> H2:
>
> =SUMPRODUCT((OFFSET($B$2,F2-1,0,G2-F2+1)<>"")/COUNTIF(OFFSET($B$2,F2-1,0,
> G2-F2+1),OFFSET($B$2,F2-1,0,G2-F2+1)&""))
>
> 2) Using a helper column...
>
> D2, copied down:
>
> =A2&"#"&B2
>
> F2:
>
> =SUMPRODUCT(($A$2:$A$13=E2)/COUNTIF($D$2:$D$13,$D$2:$D$13&""))
>
> ....where E2 contains the MonthID.
>
> Hope this helps!
>
>
> In article <[email protected]>,
> JohnV <[email protected]> wrote:
>
> > I have a data sheet that contains 15 columns I want to count unique
> > BranchNames is 1 column based upon criteria in other columns. The data sheet
> > can range from 5,000 to 50,000 rows and will vary each time I run my macros.
> >
> > The results I am looking for is to get the count of Unique BranchNames for
> > MonthID = 1 so that my result is 3 (the actual number of rows where the
> > condition 1 is met can range from 500 to 20,000 and the result I would want
> > to see is anywhere from 10 to 150). The reason is that each branchname can
> > have multiple companies and multiple orders per company.
> >
> > All the Sumproduct formula I have found return the number of rows where the
> > MonthID is 1, but not the unique count of the BranchName where the MonthID is
> > 1.
> >
> > MonthID BranchName Company
> > 1 Boston XYX
> > 1 Boston Widgets
> > 1 New York Widgets
> > 1 Seattle ABC Co
> > 2 New York Widgets
> > 2 Seattle ABC Co
> > 2 New York Widgets
> > 2 Boston XYX
> > 2 Boston Widgets
> > 3 New York Widgets
> > 3 Seattle ABC Co
> > 3 New York Widgets
> >
> > Regards,
> > JohnV
>