(Msg. 1) Posted: Wed Aug 06, 2008 8:05 am
Post subject: Filtering totals - Reps per Province Add to elertz Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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!
(Msg. 2) Posted: Wed Aug 06, 2008 8:17 am
Post subject: RE: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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!
>
(Msg. 3) Posted: Wed Aug 06, 2008 9:56 am
Post subject: Re: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Aug 6, 11:17 am, Mike H <Mi....DeleteThis@discussions.microsoft.com> 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
(Msg. 4) Posted: Wed Aug 06, 2008 10:04 am
Post subject: Re: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=COUNTIF(A2:A20,"A")
IF you use filter then use
=SUBTOTAL(3,A2:A20)
and filter on A
--
Regards,
Peo Sjoblom
"jvbelg" <jvromant RemoveThis @gmail.com> wrote in message
news:a7cc532b-0da4-4335-95de-e78abe740a8a@k30g2000hse.googlegroups.com...
On Aug 6, 11:17 am, Mike H <Mi... RemoveThis @discussions.microsoft.com> 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
(Msg. 5) Posted: Wed Aug 06, 2008 11:54 am
Post subject: Re: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <peo.sjob....RemoveThis@mvps.org> wrote:
> =COUNTIF(A2:A20,"A")
>
> IF you use filter then use
>
> =SUBTOTAL(3,A2:A20)
>
> and filter on A
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "jvbelg" <jvrom....RemoveThis@gmail.com> wrote in message
>
> news:a7cc532b-0da4-4335-95de-e78abe740a8a@k30g2000hse.googlegroups.com...
> On Aug 6, 11:17 am, Mike H <Mi....RemoveThis@discussions.microsoft.com> 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 -
(Msg. 6) Posted: Wed Aug 06, 2008 11:59 am
Post subject: Re: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Use the subtotal formula
--
Regards,
Peo Sjoblom
"jvbelg" <jvromant DeleteThis @gmail.com> wrote in message
news:57dde82e-6fde-4bc1-9a87-339acb1f0c50@c58g2000hsc.googlegroups.com...
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" <peo.sjob... DeleteThis @mvps.org> wrote:
> =COUNTIF(A2:A20,"A")
>
> IF you use filter then use
>
> =SUBTOTAL(3,A2:A20)
>
> and filter on A
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "jvbelg" <jvrom... DeleteThis @gmail.com> wrote in message
>
> news:a7cc532b-0da4-4335-95de-e78abe740a8a@k30g2000hse.googlegroups.com...
> On Aug 6, 11:17 am, Mike H <Mi... DeleteThis @discussions.microsoft.com> 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 -
(Msg. 7) Posted: Wed Aug 06, 2008 4:26 pm
Post subject: RE: Filtering totals - Reps per Province Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=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!
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office