software

I have a workbook with 2 columns, Service amp; 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.

quot;Karenquot; wrote:

gt; I have a workbook with 2 columns, Service amp; Location. Each location has up
gt; to 4 services. I need to find out how many locations have all 4 services, 3
gt; services etc. I simpy cannot figure out how to do it. I have tried the
gt; subtotal till I am crazy and cannot do it. Does anyone have any ideas? I am
gt; using Excel 2002, SP3. I would be happy to email part of the sheet if that
gt; 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!
quot;pdbergerquot; wrote:

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

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

-via135Karen Wrote:
gt; Sorry! I am a newby!
gt;
gt; Location Service
gt; 8 EL
gt; 8 WA
gt; 8 SW
gt; 8 RE
gt; 20 EL
gt; 20 SW
gt; 26 EL
gt; 26 WA
gt; 26 SW
gt; 30 EL
gt;
gt; does that help? Thank you so much!
gt; quot;pdbergerquot; wrote:
gt;
gt; gt; Karen --
gt;
gt;
gt;
gt;
gt; gt; In order for people to help, they'll need to see how you set up the
gt; columns.
gt; gt; Just a few rows recreated here should do the trick.
gt; gt;
gt; gt; quot;Karenquot; wrote:
gt; gt;
gt; gt; gt; I have a workbook with 2 columns, Service amp; Location. Each
gt; location has up
gt; gt; gt; to 4 services. I need to find out how many locations have all 4
gt; services, 3
gt; gt; gt; services etc. I simpy cannot figure out how to do it. I have
gt; tried the
gt; gt; gt; subtotal till I am crazy and cannot do it. Does anyone have any
gt; ideas? I am
gt; gt; gt; using Excel 2002, SP3. I would be happy to email part of the sheet
gt; if that
gt; gt; gt; will help. Thank you so much!--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=533026Well, It's probably inappropriate on this board but quot;I LOVE YOUquot;! Thank you
so much for your help. You will never know how much I appreciate it. I got
it! Thanks again.

quot;pdbergerquot; wrote:

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