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.


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

| improve this answer | |
  • 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

Please try COUNTIFS:

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

enter image description here

| improve this answer | |

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.

| improve this answer | |
  • 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.

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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