|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 6th, 2008, 04:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! 
|
|
#2
August 6th, 2008, 04:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#3
August 6th, 2008, 05:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#4
August 6th, 2008, 06:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#5
August 6th, 2008, 07:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 -
|
|
#6
August 6th, 2008, 07:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 -
|
|
#7
August 7th, 2008, 12:26 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
