Results 1 to 9 of 9
Thread: Countif (2003)

20050720, 19:54 #1
 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?????

20050720, 20:07 #2
 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

20050720, 20:10 #3
 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.

20050720, 20:16 #4
 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

20050720, 20:28 #5
 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

20050720, 20:55 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20050720, 22:01 #7
 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

20050721, 19:24 #8
 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.

20050721, 19:24 #9
 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.