Saturday, February 15, 2014

yogi_Count Number Of Males Of Specified Age Range Who Favor Specified Food(s)

                                         Google Spreadsheet   Post  #1528
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Feb-15-2014
post by Bele Claudiu (!category-topic/docs/spreadsheets/cDD2W8uzsgM) 
COUNTA paired with FILTER yields bad results
Hello everyone, I'm having some problems with this formula that I'm using:

I have a spreadsheet called Data, in which I have user surveys. In the Data sheet I have this following data on columns:
- Column A : Gender
- Column B : Age
- Column C : A question

I want to see how many Males between 25 and 40 have answered the question with the answer that I have in A17 in my current SpreadSheet.

=COUNTA( FILTER( Data!C2:C ; Data!C2:C = A17; Data!B2:B>25; Data!B2:B<=40 ; Data!A2:A= "Male") )

A17 is an answer that occurs 3 times for males between 25 and 40, and the formula returns 3
A18 is an answer that occurs 0 times for males between 25 and 40, and the formula returns 1

Even if I enter some random words, I still get a 1, like from this formula

=COUNTA( FILTER( Data!C2:C ; Data!C2:C = "fsagasagasgasgsa"; Data!B2:B>25; Data!B2:B<=40 ; Data!A2:A= "Male") )

Is this a bug? Is there any workaround to make this work accurately? Or am I doing something wrong here? Thanks a lot for the help.
I just made the spreadsheet to show you, having the exact same problem:

In Main Data sheet, I have Gender, Age and Favorite food

In the 2nd sheet, I try to see the number of males for each favorite food, for males between 15 and 25. There are no males in that category that like Steak and Hamburger, yet the result is 1, and if I am to remove those 2 rows in which we have males in that category that like pizza, the result for pizza would still be 1.

Thanks a lot for trying to help me!