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 ,
JohnV 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