count function help

[Karen]

I have a workbook with 2 columns, Service & Location. Each location has up
to 4 services. I need to find out how many locations have all 4 services, 3
services etc. I simpy cannot figure out how to do it. I have tried the
subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
using Excel 2002, SP3. I would be happy to email part of the sheet if that
will help. Thank you so much!

[pdberger]

Karen --
In order for people to help, they'll need to see how you set up the columns.
Just a few rows recreated here should do the trick.

"Karen" wrote:

> I have a workbook with 2 columns, Service & Location. Each location has up
> to 4 services. I need to find out how many locations have all 4 services, 3
> services etc. I simpy cannot figure out how to do it. I have tried the
> subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
> using Excel 2002, SP3. I would be happy to email part of the sheet if that
> will help. Thank you so much!

[Karen]

Sorry! I am a newby!

Location Service
8 EL
8 WA
8 SW
8 RE
20 EL
20 SW
26 EL
26 WA
26 SW
30 EL

does that help? Thank you so much!
"pdberger" wrote:

> Karen --
> In order for people to help, they'll need to see how you set up the columns.
> Just a few rows recreated here should do the trick.
>
> "Karen" wrote:
>
> > I have a workbook with 2 columns, Service & Location. Each location has up
> > to 4 services. I need to find out how many locations have all 4 services, 3
> > services etc. I simpy cannot figure out how to do it. I have tried the
> > subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
> > using Excel 2002, SP3. I would be happy to email part of the sheet if that
> > will help. Thank you so much!

[pdberger]

Karen --

Two ways.
First -- use the COUNTIF function, and essentially ignore the 'Service'
column -- after all, you don't really need it for this purpose
A B
Location Service
1 8 EL
2 8 WA
3 8 SW
4 8 RE
5 20 EL
6 20 SW
7 26 EL
8 26 WA
9 26 SW
10 30 EL

15 8 =countif($a$2:$a$10,a15)
16 20 =countif($a$2:$a$10,a16)
etc.

The second, much cooler way is to try a pivot table. Select a cell in your
table, and click Data>PivotTable. Follow the instructions, dragging the
location field into the row headers, and the service field into the data
area. (This will make sense when you try it.) Very cool.

HTH
"Karen" wrote:

> Sorry! I am a newby!
>
> Location Service
> 8 EL
> 8 WA
> 8 SW
> 8 RE
> 20 EL
> 20 SW
> 26 EL
> 26 WA
> 26 SW
> 30 EL
>
> does that help? Thank you so much!
> "pdberger" wrote:
>
> > Karen --
> > In order for people to help, they'll need to see how you set up the columns.
> > Just a few rows recreated here should do the trick.
> >
> > "Karen" wrote:
> >
> > > I have a workbook with 2 columns, Service & Location. Each location has up
> > > to 4 services. I need to find out how many locations have all 4 services, 3
> > > services etc. I simpy cannot figure out how to do it. I have tried the
> > > subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
> > > using Excel 2002, SP3. I would be happy to email part of the sheet if that
> > > will help. Thank you so much!

[via135]

hi!

=SUMPRODUCT(--(B1:B10=F1)*(--A1:A10=G1))

assuming that the service you are looking for is in F1 & the location you looking for is in G1

-via135

Sorry! I am a newby!

Location Service
8 EL
8 WA
8 SW
8 RE
20 EL
20 SW
26 EL
26 WA
26 SW
30 EL

does that help? Thank you so much!
"pdberger" wrote:

> Karen --




> In order for people to help, they'll need to see how you set up the columns.
> Just a few rows recreated here should do the trick.
>
> "Karen" wrote:
>
> > I have a workbook with 2 columns, Service & Location. Each location has up
> > to 4 services. I need to find out how many locations have all 4 services, 3
> > services etc. I simpy cannot figure out how to do it. I have tried the
> > subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
> > using Excel 2002, SP3. I would be happy to email part of the sheet if that
> > will help. Thank you so much!

[Karen]

Well, It's probably inappropriate on this board but "I LOVE YOU"! Thank you
so much for your help. You will never know how much I appreciate it. I got
it! Thanks again.

"pdberger" wrote:

> Karen --
>
> Two ways.
> First -- use the COUNTIF function, and essentially ignore the 'Service'
> column -- after all, you don't really need it for this purpose
> A B
> Location Service
> 1 8 EL
> 2 8 WA
> 3 8 SW
> 4 8 RE
> 5 20 EL
> 6 20 SW
> 7 26 EL
> 8 26 WA
> 9 26 SW
> 10 30 EL
>
> 15 8 =countif($a$2:$a$10,a15)
> 16 20 =countif($a$2:$a$10,a16)
> etc.
>
> The second, much cooler way is to try a pivot table. Select a cell in your
> table, and click Data>PivotTable. Follow the instructions, dragging the
> location field into the row headers, and the service field into the data
> area. (This will make sense when you try it.) Very cool.
>
> HTH
> "Karen" wrote:
>
> > Sorry! I am a newby!
> >
> > Location Service
> > 8 EL
> > 8 WA
> > 8 SW
> > 8 RE
> > 20 EL
> > 20 SW
> > 26 EL
> > 26 WA
> > 26 SW
> > 30 EL
> >
> > does that help? Thank you so much!
> > "pdberger" wrote:
> >
> > > Karen --
> > > In order for people to help, they'll need to see how you set up the columns.
> > > Just a few rows recreated here should do the trick.
> > >
> > > "Karen" wrote:
> > >
> > > > I have a workbook with 2 columns, Service & Location. Each location has up
> > > > to 4 services. I need to find out how many locations have all 4 services, 3
> > > > services etc. I simpy cannot figure out how to do it. I have tried the
> > > > subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
> > > > using Excel 2002, SP3. I would be happy to email part of the sheet if that
> > > > will help. Thank you so much!