Filtering totals - Reps per Province
 

I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!

Filtering totals - Reps per Province
 

Maybe

=SUMPRODUCT((A1:A20="A")*(C1:C20))

Mike

"jvbelg" wrote:

I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!

Filtering totals - Reps per Province
 

On Aug 6, 11:17*am, Mike H wrote:
Maybe

=SUMPRODUCT((A1:A20="A")*(C1:C20))

Mike



"jvbelg" wrote:
I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A *7213 * *3
A *8027 * *8
A *2230 * *4
B *3240 * *3
B *3148 * *3
B *2370 * *4
C *3121 * *3
C *3071 * *3
C *4118 * *7
C *3125 * *3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? *Thanks in advance!- Hide quoted text -

- Show quoted text -

Mike,
Thanks for your response. Your solution adds up the rep numbers [e.g.
for "A" it shows a total of 15], instead of counting them. The total
should be 3.
Cheers - Jan

Filtering totals - Reps per Province
 

=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A



--


Regards,


Peo Sjoblom

"jvbelg" wrote in message
...
On Aug 6, 11:17 am, Mike H wrote:
Maybe

=SUMPRODUCT((A1:A20="A")*(C1:C20))

Mike



"jvbelg" wrote:
I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!- Hide
quoted text -

- Show quoted text -

Mike,
Thanks for your response. Your solution adds up the rep numbers [e.g.
for "A" it shows a total of 15], instead of counting them. The total
should be 3.
Cheers - Jan

Filtering totals - Reps per Province
 

Peo,
The result is incorrect when I am applying the filter. For example,
it will show 4, when filtering on province "C". It should show 2 [2
unique reps working that province.]
Cheers - Jan



On Aug 6, 1:04*pm, "Peo Sjoblom" wrote:
=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A

--

Regards,

Peo Sjoblom

"jvbelg" wrote in message

...
On Aug 6, 11:17 am, Mike H wrote:





Maybe

=SUMPRODUCT((A1:A20="A")*(C1:C20))

Mike

"jvbelg" wrote:
I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!- Hide
quoted text -

- Show quoted text -

Mike,
Thanks for your response. *Your solution adds up the rep numbers [e.g.
for "A" it shows a total of 15], instead of counting them. *The total
should be 3.
Cheers - Jan- Hide quoted text -

- Show quoted text -

Filtering totals - Reps per Province
 

Use the subtotal formula

--


Regards,


Peo Sjoblom

"jvbelg" wrote in message
...
Peo,
The result is incorrect when I am applying the filter. For example,
it will show 4, when filtering on province "C". It should show 2 [2
unique reps working that province.]
Cheers - Jan



On Aug 6, 1:04 pm, "Peo Sjoblom" wrote:
=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A

--

Regards,

Peo Sjoblom

"jvbelg" wrote in message

...
On Aug 6, 11:17 am, Mike H wrote:





Maybe

=SUMPRODUCT((A1:A20="A")*(C1:C20))

Mike

"jvbelg" wrote:
I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!- Hide
quoted text -

- Show quoted text -

Mike,
Thanks for your response. Your solution adds up the rep numbers [e.g.
for "A" it shows a total of 15], instead of counting them. The total
should be 3.
Cheers - Jan- Hide quoted text -

- Show quoted text -

Filtering totals - Reps per Province
 

=COUNT(1/FREQUENCY(IF(A1:A10="c",C1:C10),C1:C10))

ctrl+shift+enter, not just enter


"jvbelg" wrote:

I have 3 columns: Provinces, Customer numbers, and list of reps
servicing these customers.

A 7213 3
A 8027 8
A 2230 4
B 3240 3
B 3148 3
B 2370 4
C 3121 3
C 3071 3
C 4118 7
C 3125 3

When I filter this list [on provinces], I can tally a total of
different customers I have in a particular province [e.g. for Province
B, the total shows 3].
What I would like to see also is a list of how many reps are active in
that particular province [e.g. for Province B, it would show 2 - reps
#3 and #4; while for Province A, it would show 3 - reps #3, #8, and
#4].

Any suggestion for a formula to achieve this? Thanks in advance!