How to use the COUNTIF function in Google Sheets
In this article, we would have a look at how to use the COUNTIF function in Google Sheets and see some working examples.
Table of Contents
What is the COUNTIF function in Google Sheets
Syntax of the COUNTIF function
Usage and examples
What is the COUNTIF function in Google Sheets
The COUNTIF comes from two words, count and if. COUNTIF function in Google Sheets returns the count of entries based on some condition. This is helpful when you want to analyse your data without actually checking each and every entry. For example, from a list of sales records for employees, you can see how many sales per person were over the given amount.
This article would cover all the basic usage of the COUNTIF function in Google Sheets. For a tutorial on how to count non-empty cells, check this.
Syntax
The syntax for the COUNTIF function in Google Sheets is as given under:
=COUNTIF(range, criterion)
- range: the range to apply COUNTIF to
- criterion: The applied criteria to count the numbers
If the entries are numbers:-
- The criterion can be a string or a number. In the case of a number, it checks for an equality operator.
- You can check for greater than (>), greater than equal to (>=), not equal to (<>) etc in combination with numbers. These should however be enclosed in double quotes. For example “>=50”.
- If you want to use references along with comparison operators, you need to use & (ampersand) to join the two expressions. However, only the comparison operators will be in double quotes. For eg “<>”&A1.
If the entries are texts:-
- In this case, the criterion must be a string. By default, it would check for equality but you can use regex rules to count for different cases.
- The queries must be enclosed in double quotes.
Usage and Examples
Let’s say we have a list of movie data. The list contains the Movie title, Genre, Director, Budget and box office Collections. Let’s say we have a set of queries that we need to know.
Count movies with a Budget greater than 10 million USD
This is pretty simple. We have the budget data for all the movies. We simply select the range of the column that the data resides in and the criterion for the example would be >10,000,000. The final formula will be as under.
=COUNTIF(E2:E509, “>10000000”)
Count of movies with budget greater than 10 million USD
Count of movies with Genre “Drama”
Since the entries for this column are in strings, the criterion will also be a string. The range would be the range of the column of genre and the criterion would be “Drama”. Hence the resultant formula would look like this.
=COUNTIF(C2:C509, “Drama”)
Count of movies with Genre “Drama”
Count of movies with director’s name starting with David
Since there can be many names starting from David, we will have to use some RegEx rules here. We can write the query as
=COUNTIF(D2:D509, “David*”)
This will return all the entries which start with “David”.
Count of movies with director’s name starting with David
Conclusion
Hence, we saw how to use the COUNTIF function in Google Sheets. COUNTIF is a very useful function for analytics which can be combined with other functions and concepts. This article provides a brief description of the functionality it provides you with. It can be combined with regular expressions to perform more niche and customised searches in text.
You can also check out the official documentation for COUNTIF in Google Sheets documentation.
