Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

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

share|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
add comment

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.

share|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
add comment

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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