complex count
 

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.

complex count
 

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" wrote in message
...
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.

complex count
 

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

"FSmitty" wrote in message
...
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.

complex count
 

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.

"Biff" wrote:

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

"FSmitty" wrote in message
...
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.

complex count
 

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:B20099<200)), I get #VALUE as an answer
to the formula instead of a count.

"Bob Phillips" wrote:

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" wrote in message
...
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.

complex count
 

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

"FSmitty" wrote in message
...
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:B20099<200)), I get #VALUE as an
answer
to the formula instead of a count.

"Bob Phillips" wrote:

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" wrote in message
...
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.

complex count
 

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


"Biff" wrote:

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

"FSmitty" wrote in message
...
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:B20099<200)), I get #VALUE as an
answer
to the formula instead of a count.

"Bob Phillips" wrote:

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" wrote in message
...
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.

complex count
 

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

"FSmitty" wrote in message
...
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


"Biff" wrote:

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

"FSmitty" wrote in message
...
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:B20099<200)), I get #VALUE as an
answer
to the formula instead of a count.

"Bob Phillips" wrote:

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" wrote in message
...
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.