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

![Google Sheets Table](https://i.stack.imgur.com/9p9Pl.png)

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?

Answer: 
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](https://docs.google.com/spreadsheet/ccc?key=0Avea1NXBTibYdFB5TVF0MVVXdVBfX0xuYXFFZXNXWlE&usp=sharing) I set up.
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](https://i.stack.imgur.com/Tm08o.jpg)](https://i.stack.imgur.com/Tm08o.jpg)