0

I have two columns that are generated from a Google Forms:

Google Sheets Table

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?

|improve this question
  • Are there only two possible values for City, or can it be anything? – Vidar S. Ramdal Nov 12 '13 at 14:25
  • @VidarS.Ramdal For this example, lets assume there are only two cities. – nicholaschris Nov 12 '13 at 14:46
1

You could concatenate the strings, so that the row Male | City A is represented as MaleCity A, and count the occurrences of those strings.

Given that column B contains either Male or Female, and column C contains either City A or City B, you could count males/females in each city:

  • Number of males in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity A")
  • Number of females in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity A")
  • Number of males in City B: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity B")
  • Number of females in City B =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity B")

The percentage of males/females in each city can then easily be calculated.

See the example spreadsheet I set up.

|improve this answer
  • This looks like a good solution. It works. As I see it, the same number of steps are required. – nicholaschris Nov 14 '13 at 11:32
  • Good to hear! If you're happy with the answer, you could click Accept. Or wait a few days to see if other possibilities show up. – Vidar S. Ramdal Nov 14 '13 at 11:52
  • I will wait a few days and then accept. Thanks @Vidar! – nicholaschris Nov 14 '13 at 13:32
0

Concerning:

So I thought I could use COUNTIFS as in Excel but that is not available.

But it is available. A pivot table might be more convenient (on the right) but a similar table can be constructed with:

=countifs($A:$A,E$1,$B:$B,$D2)

as shown in the middle (yellow labels):

enter image description here

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.