Filtering totals - Reps per Province

[jvbelg]

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!

 

[Mike H]

Maybe

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

Mike

 

[jvbelg]

Maybe

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

Mike

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

 

[Peo Sjoblom]

=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A



--


Regards,


Peo Sjoblom

Maybe

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

Mike

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

 

[jvbelg]

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

=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A

--

Regards,

Peo Sjoblom

Maybe

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

Mike

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 -

 

[Peo Sjoblom]

Use the subtotal formula

--


Regards,


Peo Sjoblom

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

=COUNTIF(A2:A20,"A")

IF you use filter then use

=SUBTOTAL(3,A2:A20)

and filter on A

--

Regards,

Peo Sjoblom

Maybe

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

Mike

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 -

 

[Teethless mama]

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

ctrl+shift+enter, not just enter