+ Reply to Thread
Results 1 to 3 of 3

Using If Then to count multiple criteria

  1. #1
    MentalDrow
    Guest

    Using If Then to count multiple criteria

    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


  2. #2
    Jake Marx
    Guest

    Re: Using If Then to count multiple criteria

    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:D$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]


    MentalDrow wrote:
    > 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




  3. #3
    MentalDrow
    Guest

    Re: Using If Then to count multiple criteria

    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:D$7)

    to read as this:

    =SUMIF($B$2:$B$7,$A9,D$2:D$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
    "Jake Marx" wrote:

    > 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:D$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]
    >
    >
    > MentalDrow wrote:
    > > 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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1