Using If Then to count multiple criteria

[Guest]

Hi All,

I'm trying to find a way to calculate occupancy for a dorm base on various
criteria. Currently, the information is sorted by Dorm# and Room# which is
fine as there are multiple dorms on the worksheet.

Example

Column A Column B Column C
Dorm # Room # Age
1304 115 21
1304 115 22
1304 116 21
1304 117 22
1304 118 23
1304 118 21

I have a range from age 21-25 years old and want to determine the number of
folks in each age group with and without a room mate. I'm not so much
concerned that those with room mates have someone the same age as them in the
rooms but if the room mate is a different age, each variable representing the
age group would receive a mark in the Double Occupancy cell. In the example
above it would calculated in this manner:

Double Occupancy Single Occupancy
21 - 2 1
22 - 1 1
23 - 1 0

I've found a way to pull the information only if the Dorm=1304 and Room <300
now I just need to pull the other information as indicated above. Any help?

Chaz

 

[Jake Marx]

Hi Chaz,

If you don't care about what dorm room the students are in (only age &
occupancy type), then it's not too difficult. The easiest way is to set up
2 "helper" columns that will tally the count of single and double occupancy
for the list. For example, in column D, you could enter this to track
single occupancy (1 is yes, 0 is no...copy this formula down the column):

=IF(COUNTIF($B$2:$B$7,$B2)=2,0,1)

Then in column E, you could track double-occupancy (copy this formula down
the column):

=IF(COUNTIF($B$2:$B$7,$B2)=1,0,1)

Now, you could list your ages below (let's say you put 21, 22, and 23 in A9,
A10, and A11, respectively). If you want the counts of single and double
occupany occurences for each age, you could use the following formulas for B
(single) and C (double):

=SUMIF($C$2:$C$7,$A9,D$2$7)
=SUMIF($C$2:$C$7,$A9,E$2:E$7)

These formulas could be combined so you don't need the helper columns, but I
think it's easier to follow this way. And it would be a good exercise for
you to combine them so that you understand how it works. Hopefully this
makes sense and I understood your question correctly!

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Thanks for the quick reply Jake. I've used plenty of formulas in Excel so I
have an understanding of what you're showing me here. I assume you meant for
this line

=SUMIF($C$2:$C$7,$A9,D$2$7)

to read as this:

=SUMIF($B$2:$B$7,$A9,D$2$7)
I'll look these over and see what I can do with them. However, I think I
should have mentioned that I'm doing this using Macros and want it all to run
in the background. I should be able to easily modify these lines once I look
them over more thoroughly and figure out how they actually function. If I
have any further questions about how to modify this I'll be sure to post it
here.

Thanks,

Chaz

Hi Chaz,

If you don't care about what dorm room the students are in (only age &
occupancy type), then it's not too difficult. The easiest way is to set up
2 "helper" columns that will tally the count of single and double occupancy
for the list. For example, in column D, you could enter this to track
single occupancy (1 is yes, 0 is no...copy this formula down the column):

=IF(COUNTIF($B$2:$B$7,$B2)=2,0,1)

Then in column E, you could track double-occupancy (copy this formula down
the column):

=IF(COUNTIF($B$2:$B$7,$B2)=1,0,1)

Now, you could list your ages below (let's say you put 21, 22, and 23 in A9,
A10, and A11, respectively). If you want the counts of single and double
occupany occurences for each age, you could use the following formulas for B
(single) and C (double):

=SUMIF($C$2:$C$7,$A9,D$2$7)
=SUMIF($C$2:$C$7,$A9,E$2:E$7)

These formulas could be combined so you don't need the helper columns, but I
think it's easier to follow this way. And it would be a good exercise for
you to combine them so that you understand how it works. Hopefully this
makes sense and I understood your question correctly!

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

I'm trying to find a way to calculate occupancy for a dorm base on
various criteria. Currently, the information is sorted by Dorm# and
Room# which is fine as there are multiple dorms on the worksheet.

Example

Column A Column B Column C
Dorm # Room # Age
1304 115 21
1304 115 22
1304 116 21
1304 117 22
1304 118 23
1304 118 21

I have a range from age 21-25 years old and want to determine the
number of folks in each age group with and without a room mate. I'm
not so much concerned that those with room mates have someone the
same age as them in the rooms but if the room mate is a different
age, each variable representing the age group would receive a mark in
the Double Occupancy cell. In the example above it would calculated
in this manner:

Double Occupancy Single Occupancy
21 - 2 1
22 - 1 1
23 - 1 0

I've found a way to pull the information only if the Dorm=1304 and
Room <300 now I just need to pull the other information as indicated
above. Any help?

Chaz