Table of Contents
The QUERY function in Google Sheets can be used to count the number of rows in a range of cells. This function can also be used to query and filter data, calculate sums and averages, and join related data from multiple sheets. To use the QUERY function, you must provide the range of cells you want to count, a text string that contains the search criteria, and the criteria for counting the rows. The QUERY function also allows you to use logical operators such as AND, OR, and NOT in the criteria to refine the results of the query.
You can use the COUNT() function in a Google Sheets query to count the number of rows that meet certain conditions.
Method 1: Count Total Rows
=QUERY(A1:C13, "select count(A)")
Method 2: Count Total Rows that Meet One Criteria
=QUERY(A1:C13, "select count(A) where B>100")
Method 3: Count Total Rows that Meet Multiple Criteria
=QUERY(A1:C13, "select count(A) where A='Mavs' OR B>100") =QUERY(A1:C13, "select count(A) where A='Mavs' AND B>100")
The following examples show how to use each of these methods in practice.
Method 1: Count Total Rows
We can use the following formula to count the total number of teams:
We can see that there are 12 total teams.
Example 2: Count Total Rows that Meet One Criteria
We can use the following formula to count the total number of teams where the points is greater than 100:
We can see that there are 2 teams that have over 100 points.
We can use the following formula to count the total number of teams that have less than 100 points:
We can see that 10 teams have less than 100 points.
Example 3: Count Total Rows that Meet Multiple Criteria
We can use the following formula to count the total number of teams where the team name is “Mavs” or where the points is greater than 100:
We can see that 3 teams meet either of these criteria.
We can use the following formula to count the total number of teams where the points is greater than 90 and the rebounds is greater than 30:
We can see that 2 teams meet both of these criteria.
The following tutorials explain how to perform other common operations with Google Sheets queries: