count function help

K

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

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

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

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
 
V

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
 
K

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