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.

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

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FSmitty" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
>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.

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

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.

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))
>
> etc.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "FSmitty" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Bob Phillips" wrote:
>
>> =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))
>>
>> etc.
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "FSmitty" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Bob Phillips" wrote:
> >
> >> =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))
> >>
> >> etc.
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (remove nothere from email address if mailing direct)
> >>
> >> "FSmitty" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))
>> >>
>> >> etc.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Bob Phillips
>> >>
>> >> (remove nothere from email address if mailing direct)
>> >>
>> >> "FSmitty" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>