How to Use COUNTIF Formula in Google Sheets

You can use COUNTIF in Google Sheets alongside other similar functions. When we type “=COUNT” in Google Sheets cell we will see a variety of many awesome functions that allow us to analyze our data. Each of those functions’ syntax is very well described directly in Google Sheets.

To see the full description of each of the functions just click the small question mark on the left.

When you click the question mark sign, the basic information about the COUNTIF in Google Sheets will be there.

As a main difference to Excel functions, we have many more options related to the COUNT function, a very useful one is the COUNTUNIQUE function helps to get the result immediately while in Excel we would have to make a very complex IF statement.

How to Apply COUNTIF in Google Sheets

Let’s start from the basics. The Countif Google Sheets formula allows us to count a total number of values in a specific range if they fulfill a set condition.

Our condition can be dynamic and come from a cell (example1) or can be just directly typed into the formula.

Our condition can be a bit more complex. What if we want to count the people hired after a specific date? The solution is simple but we need to know how to write it in a proper way.

When we want to compare values to numbers or dates. Our main possibilities are:

“>” – greater than, “<” – smaller than, “>=” – greater or equal, and “<=” – smaller or equal. We need to combine them with the “&” so the condition is understood by Google Sheets formula.

COUNTIF Function with Multiple Conditions

If we want a bit different approach from the limits of Countif Google Sheets function and create a bit more controlled condition, we can use the COUNTIFS formula that will help us to set multiple conditions.

We can set completely different conditions related to two separate ranges as in example 5 below. The main rule that we need to remember is the ranges must have the same size.

That means if I set 2 conditions one for column C in a range C3:C10 then the other condition also should be within the range of 3 to 10 rows.

If we want to count rows that contain some range of dates this also calls for the COUNTIFS formula because like in example 6 we need to choose a date that is equal to or larger than the 1st of March and smaller than the 1st of April.

Using OR Function with COUNTIF in Google Sheets

What if we want to include OR formula within COUNTIF in Google Sheets? Let’s say I want to count if the Department is Sales or Hiring date is before 1st of March. We need a bit more logic there. I will create 2 separate COUNTIF(S):

First I want to count all the rows before the 1st of March.

The second one needs 2 conditions: the department should be equal to “Sales” but only after the 1st of March since all the earlier dates we need to count in no matter the source.

And the last but really important usage is counting the not empty cells or distinguishing numbers from text.

In example 9 we count only the rows that contain full information, and the date is actually a date. So all the conditions only check if the value of the cell is not empty “<>”&

And the hiring date checks to find out if the value is numeric and higher than 1. The last example is not a COUNTIF Google Sheets function. But it takes the condition by comparing the COUNT function. The function counts all the numerical values. The COUNTA formula counts all the nonempty cells.

In conclusion, understanding how to use the COUNTIF formula in Google Sheets can be a powerful tool for data analysis. By setting dynamic and multiple conditions, you can quickly and easily extract valuable information from your data. Count the number of items with specific criteria or analyze complex data sets, the COUNTIF formula is an essential function to master in Google Sheets.

To learn more about countig formula, you can watch our tutorial video:

That’s all. Thanks for reading. Don’t forget to check our other Google Sheets tips.