I have two columns that are generated from a Google Forms:
I would like to create two pie charts, one for City A and one for City B, showing the percentages of male and females from those cities. I am trying to get the count of:
- all males in City A,
- males in City B,
- females in City A, and
- females in City B
in order to create the pie charts. I can then save these values in a new sheet and generate the pie charts (or bar charts). So I thought I could use COUNTIFS as in Excel but that is not available. I have tried this formula:
=COUNTA(FILTER('Form Responses'! B2:B100, 'Form Responses'! B2:B100="Male", 'Form Responses'! C2:C100, "City B" ))
which didn't seem to get the right answer.
This formula seems to work:
=INDEX( SUMPRODUCT( ('Form Responses'! B2:B100 = "Male") * ('Form Responses'! C2:C100 ="City B") );1)
and I have been told that:
=COUNTA(FILTER('Form Responses'! B2:B100;('Form Responses'! B2:B100="Male")*('Form Responses'! C2:C100="City B")))
would also work.
Is that the best way to go about getting the information I need for the pie charts, or is there a simpler way of doing this?