complex count

F

FSmitty

I have two columns in a spreadsheet. One column has one word descriptions
e.g. falls, and the other column has the location e.g 100 hall. I want to
compile the number of falls for example that occurred on different halls e.g
100, 200, 300 halls. Remember that the column with descriptions has several
descriptions but I just want falls.
 
B

Bob Phillips

=SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Biff

What result would you expect from this sample:

fall..........100 hall
fall..........100 hall
full..........100 ball
fall..........200 hall
fall..........200 hall
fall..........300 hall

Biff
 
F

FSmitty

Biff,
The column with 'falls' has numerous other classifications such as 'tears',
'skin', 'bruises' etc. I want a formual that will count only falls for lets
say the 100 hall. Thanks for your help.
 
F

FSmitty

Bob,
Now to add to the formula, Lets say that the 100 hall has a range of room
numbers from 100 to 199. I want to count all the "falls" that occurred within
that range of numbers (100 to 199). When I use the formula
=SUMPRODUCT(--(A:A200="falls"),--(B2:B200>99<200)), I get #VALUE as an answer
to the formula instead of a count.
 
B

Biff

Hi!

falls 100 hall

Is that data in 3 cells?

Your original post makes it sound as though it's only in 2 cells:

1 cell = falls
1 cell = 100 hall

Biff
 
F

FSmitty

Hi again,
The data is located in two columns. One column has incidents e.g. falls,
tears, bruises, etc. while the other column is the room number which range
from 1 to 399. I want to count the # of falls per wing (100 hall) which would
be any fall occurring for anyone in room 100 through 199. Hope this helps.
Thanks.

Brent
 
B

Biff

Ok, you can make this much easier if you split the data into 3 columns:

falls | 100 | hall

To count falls, 100, hall:

=SUMPRODUCT(--(A1:A10="falls"),--(B1:B10=100),--(C1:C10="hall"))

To count falls, >=100, <=199, hall:

=SUMPRODUCT(--(A1:A10="falls"),--(B1:B10>=100),--(B1:B10<=199),--(C1:C10="hall"))

It's better if you use cells to hold the different criteria:

D1 = falls
D2 = hall
E1 = 100
F1 = 199

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1),--(C1:C10=D2))

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10>=E1),--(B1:B10<=F1),--(C1:C10=D2))

Biff
 
Top