# COUNTIF but when multiple conditions are met

I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough thing to search.

I currently have a COUNTIF formula that looks like this:

``````COUNTIF( 'Survey Data'!F2:F200; "Divisional Exec" )
``````

This works fine. But I would like to refine that. So I want to count the number of times a string appears when `COLUMN A = 'ACT'` and when `COLUMN F = 'Divisional Exec'` in the same row.

Is this possible to do in Google Spreadsheets? If so how do I do it?

I have tried Multiple Conditions on a CountIF? but can't seem to get it to work for me.

`=arrayformula(SUM(('Survey Data'!F2:F200="Divisional Exec")*('Survey Data'!A2:A200="Act")))`

In Excel - assuming Excel 2007 or later you can use `COUNTIFS`

`=COUNTIFS('Survey Data'!F2:F200,"Divisional Exec",'Survey Data'!A2:A200,"Act")`

• I get a `#NAME?` error with this. – Tom Maitland Feb 6 '13 at 22:08
• Hello Tom, Are you using the formula in google-spreadsheets - I tested in there and it works OK? It won't work in Excel - I'll add an excel version...... – barry houdini Feb 6 '13 at 22:15
• Brilliant, works like a charm on Google Sheets. – David Brossard Nov 22 '19 at 18:32

``````=countifs(A:A,"ACT",F:F,"Divisional Exec")
`````` This is another way of doing it:

``````=COUNTA(FILTER('Survey Data'!F2:F200,'Survey Data'!F2:F200="Divisional Exec",
'Survey Data'!A2:A200="Act"))
``````

but, as Jean-Christophe Meillaud stipulated, it yields 1 if the criteria aren't met. The following formula is yet another way of doing things:

``````=IFERROR(INDEX(QUERY(A2:F200,"SELECT COUNT(F) WHERE F='Divisional Exec'
AND A='Act'"),2,1),"")
``````

The `INDEX` formula removes the header ("count") and the `IFERROR` returns nothing.

• That do the trick, but for some reason, even if none of the conditions are verified, result is 1 at least, any idea ? – Jean-Christophe Meillaud May 25 '13 at 13:08
• @Jean-ChristopheMeillaud I revised my answer. Thanks for posting !! – Jacob Jan Tuinstra May 26 '13 at 20:17

I have used an additional field to trigger the true condition of countif().

Use another formula to evaluate column F and column A and put the results in the new column. Then use the countif() formula to summarize the results.

You might also investigate the use of a pivottable.

• Not really able to do this because the data table is an imported survey and I need it easy for people to reimport and update. There are also about 30 places I would need to put this formula in, which would extrapolate out to a lot of columns. – Tom Maitland Feb 6 '13 at 22:09