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

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.

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