yogi_Compute Number Of All Female And Male Students For Certain Attributes In Specified Ranges

                                          Google Spreadsheet   Post  #966

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 11, 2013

user Marschal Fazio said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/JvfbJw-9vX8)
QUERY function to replace COUNTA function to count cells based on multiple criteria 

Here is my Spreadsheet. What I am trying to do is filter the males and females and count the number of individuals with a TRC between two integers. This was how we attempted to do this and it returned 1 when the cells should have returned a 0.

=ArrayFormula(COUNTa(FILTER(Sheet1!D:D,(sheet1!D:D="Male")*(sheet1!I:I>=E9)*(sheet1!I:I<=F9))))

=ArrayFormula(COUNTa(FILTER(Sheet1!D:D,(sheet1!D:D="Female")*(sheet1!I:I>=E9)*(sheet1!I:I<=F9))))

Can I use a query function instead. If so how would I go about writing the formula. I understand query also has problems when results are 0 but returns an Error which could be fixed with IFERROR function.

---

I know this is not correct but I want this basic idea

=IFERROR(QUERY(Sheet1!D:I select count (D) where D="Male" and I>="0" and I<="20"), 0)

-----------------------------------------------------------------------------------------------------------

following is a solution to the problem