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?

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

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.

• This looks like a good solution. It works. As I see it, the same number of steps are required. 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. Nov 14 '13 at 11:52
• I will wait a few days and then accept. Thanks @Vidar! Nov 14 '13 at 13:32

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): 