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.

4 Answers 4


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

  • 1
    I get a #NAME? error with this. Feb 6, 2013 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...... Feb 6, 2013 at 22:15
  • Brilliant, works like a charm on Google Sheets. Nov 22, 2019 at 18:32

Please try COUNTIFS:

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

enter image description here


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:

  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 ? May 25, 2013 at 13:08
  • @Jean-ChristopheMeillaud I revised my answer. Thanks for posting !!
    – Jacob Jan
    May 26, 2013 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. Feb 6, 2013 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.