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 filterautofilter 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" wrote in message
...
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