Formula to check a criteria in a specific range.

Suppose you want to get "FAIL" in a cell if a certain criteria is met in another range of cells. So in the table below, J4 would return the text FAIL, and J5 would return the text PASS.

Now, paste this formula in cell J4

=if(COUNTIF(C4:I4,"X")=0,"PASS","FAIL") 

And this formula in cell J5

=if(COUNTIF(C5:I5,"X")=0,"PASS","FAIL") 

Here function "COUNTIF" will search for the criteria "X" and if this criteria returns 0, it means student has passed all areas and J5 will displays "PASS".

I hope the above example will make you understand how to use the formula "CountIf" in Google Spreadsheets.