COUNTIF but when multiple conditions are met

Question

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.

Answer 1

Try this formula in google - spreadsheets

=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")

Answer 2

Please try COUNTIFS:

=countifs(A:A,"ACT",F:F,"Divisional Exec")

Answer 3

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.

Answer 4

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.