Results 1 to 9 of 9

Thread: Countif (2003)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Countif (2003)

    I've attached a small sample of what I have. I work for a school district with 800+ temporary buildings. Some of these temporary buildings have 1 classroom (960 sq ft) and some have 2 classrooms (1440 sq ft). I need a total count of the number of buildings that are considered temporary - but I have to show the sq feet of each classroom - hence, if I do it the way I have it now, I get a false building count - too many.

    What to do, what to do?????

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    What count should you have gotten in your example, and why? If I understand your explanation, the example should have 9 buildings (the answer you got), but there would be 13 classrooms. Neither of the answers I get are less than your answer of 9, which is what your question indicates it should be.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    The answer should have been 6 buildings - 4 with 1 classroom and 2 with 2 classrooms - the 720 square feet per classroom figures represent 1/2 of a 1440 sq ft building.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    Should that maybe be 5 with 2 classrooms and 2 with 1 classroom for a total of 7? If so, then try this formula:

    <code>
    =COUNTIF(C2:C16,"yes")-COUNTIF(B2:B16,720)/2
    </code>

    That formula assumes that no no buliding with 720 sq ft is not a portable, and that all with 720 have 2 classrooms.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    I'm wrong - but so are you - it should be 7 buildings - 5 with 1 classroom and 2 with 2 classrooms. A building with one classroom is 960 square feet. A building with 2 classrooms is 1440 square feet, but I have to show each classroom separately with it's very own square footage - hence the square feet figures of 720 - 1/2 of 1440. Bottom line HAS to be the total number of temporary buildings, regardless of what the square feet happens to be - that's the goal, and why I just counted the word Yes.

    Thanks for trying to help

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Countif (2003)

    Still, Legare's formula arrives at the correct answer, in spite of your description. Another possibility is

    =SUMPRODUCT((C2:C16="Yes")*(B2:B16=960))+SUMPRODUC T((C2:C16="Yes")*(B2:B16=720))/2

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    Yea, I got those backward, but I came up with the correct total and so does my formula.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    Thanks again to both of you - it does work.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2003)

    Thanks again so much for your help. It does work, of course.

Posting Permissions

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