software

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)gt;0,1))

entered with ctrl shift amp; 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 filtergt;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

nwexcelsolutions.com

quot;JohnVquot; gt; wrote in message
...
gt;I have a data sheet that contains 15 columns I want to count unique
gt; BranchNames is 1 column based upon criteria in other columns. The data
gt; sheet
gt; can range from 5,000 to 50,000 rows and will vary each time I run my
gt; macros.
gt;
gt; The results I am looking for is to get the count of Unique BranchNames for
gt; MonthID = 1 so that my result is 3 (the actual number of rows where the
gt; condition 1 is met can range from 500 to 20,000 and the result I would
gt; want
gt; to see is anywhere from 10 to 150). The reason is that each branchname can
gt; have multiple companies and multiple orders per company.
gt;
gt; All the Sumproduct formula I have found return the number of rows where
gt; the
gt; MonthID is 1, but not the unique count of the BranchName where the MonthID
gt; is
gt; 1.
gt;
gt; MonthID BranchName Company
gt; 1 Boston XYX
gt; 1 Boston Widgets
gt; 1 New York Widgets
gt; 1 Seattle ABC Co
gt; 2 New York Widgets
gt; 2 Seattle ABC Co
gt; 2 New York Widgets
gt; 2 Boston XYX
gt; 2 Boston Widgets
gt; 3 New York Widgets
gt; 3 Seattle ABC Co
gt; 3 New York Widgets
gt;
gt; Regards,
gt; JohnV
In addition to the suggestions made by Peo, assuming that A213
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)lt;gt;quot;quot;)/COUNTIF(OFFSET($B$2,F2-1,0,
G2-F2 1),OFFSET($B$2,F2-1,0,G2-F2 1)amp;quot;quot;))

2) Using a helper column...

D2, copied down:

=A2amp;quot;#quot;amp;B2

F2:

=SUMPRODUCT(($A$2:$A$13=E2)/COUNTIF($D$2:$D$13,$D$2:$D$13amp;quot;quot;))

....where E2 contains the MonthID.

Hope this helps!In article gt;,
JohnV gt; wrote:

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

quot;Domenicquot; wrote:

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