Tuesday, 27 August 2013

Google Spreadsheets - Count word occurrences

I'm a big fan of Google Docs, I think it is an incredible collaboration tool, however it got problems...

Today I was working on a spreadsheet and I wanted to count the occurrences of a word in a cell interval, this should be a simple task, right?
Infact, there's a useful function named COUNTIF(interval, criteria) that does exactly this, in interval you specify the cells to be checked, in criteria you specify the number, string or cell that you want to count in the specified interval. The only problem was...it wasn't working! It just said me #ERROR and asking for more details resulted in a generic "Analysis Error".
After some research I discovered that the problem was simply that, as a parameter separator, the comma works only in the US, for other countries you have to use the ";"!
So the right syntax is: COUNTIF(interval; criteria).
Here are some working examples:
  • =COUNTIF(L5:L450; "OK")
  • =COUNTIF(L5:L450; D45)
  • =COUNTIF(L5:L450; 12)
Bonus tip: if you want to count not empty cells use the COUNTA(interval) function!