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