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 --
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!

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 --

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 DataPivotTable. 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!

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


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
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=533026

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 DataPivotTable. 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!