The COUNTIFS function can be used to count cells between two numbers in Google Sheets.
Syntax= COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])criteria_range1 – The range to check using criterion1.criterion1 – The condition or test to apply to criteria_range1.criteria_range2, criterion2... – these are additional ranges and criteria to check.
Note: The COUNTIFS function is designed to take several ranges and criteria but any additional ranges must contain the same number of rows and columns as criteria_range1.
Examples=COUNTIFS(A1:A25, ">10", A1:A25, "<20")
//Returns the count cells with values greater than 10 and less than 20 from the range A1:A25.=COUNTIFS(D1:D100, "No")
//Returns the count of a single range D1:D100 depending on a single criterion, ie, those with values No.
Click HERE to make a copy of the spreadsheet with formulas and example.
Contents
- Introduction
- How to count cells between two numbers in Google Sheets
- Method 1: Using the COUNTA function
- Methos 2: Using the COUNTIFS function
Introduction
Sometimes our work in Google Sheets requires us to count the number of cells that fall in between two values, either in sorted or unsorted datasets. This can quickly turn into a mean task as manually counting each cell isn’t a very productive option, especially in large datasets.
Assuming we have a data set that represents the daily earnings of employees in a certain company, and we want to count the number of employees that earned money between a certain range on the previous day, how exactly do we go about it supposing that the number of employees is in the hundreds?
In this tutorial, we will look at several ways to count cells between two numbers in Google Sheets. Essentially, we will find the number of cells in a specified range.
How to count cells between two numbers in Google Sheets
Luckily, we can use some functions to perform this task in Google Sheets. The COUNT function and its variants such as COUNTIFS, COUNTA, and COUNT UNIQUE all serve various purposes and are useful for counting operations.
Let us count the number of cells between 500 USD and 1000 USD. Since each cell represents an employee, we are trying to find how many earnings fall between the range 500<= x <= 1000.
Method 1: Count cells between two numbers in Google Sheets using the COUNTA function
This is an easy way to count cells in Google Sheets. But we need to first sort the data, and then highlight the range.
- Select the entire data range as shown below.
- Go to Data ➡ Sort range ➡ Advanced range sorting options.
A sorting menu, as shown below, will appear. Here we can customize the sorting option.
- Change the Sort by option to Column C, the column containing the numbers.
- The order is set to A-Z by default–ie, ascending order–so we leave it at that.
- Click the Sort button.
This will rearrange the data in the table in ascending order of Column C.
Now, we will use the COUNTA function to count the number of cells between 500 and 1000.
- Enter the COUNTA formula.
- For the range, select cells C9:C19.
=COUNTA(C9:C19)
- Press the enter key.
The above method works, but you can agree it’s a bit tedious. Are there better ways to do this? Yes, one that involves fewer steps.
Method 2: Count cells between two numbers in Google Sheets using the COUNTIFS function
This is a much better approach as it lets us quickly perform the task in the previous example without needing to sort or manually select a range.
The function will go through the entire range and return the count for cells that fall within the specified range.
Step 1: Enter the formula
Enter the COUNTIFS function in an empty cell
- Begin to type = COUNTIFS
- Google Sheets will auto-suggest the formula
- Select it from the menu
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])Step 2: Provide a range for the COUNTIFS function
For the range, we don’t need to manually select a subset of the dataset as we did in the previous method, we simply provide the entire dataset–which is C5:C19.
Step 3: Provide a criterion to count cells between two numbers in Google Sheets
This is where the function gets interesting. For the criterion, we will provide our range of interest which is 500 to 1000 to the function.
To do this we need two criteria:
- Data must include numbers that are equal to or greater than the minimum value of 500, i.e X >= 500.
- Data must include numbers that are less than or equal to the maximum value 1000, i.e X <= 1000.
We need to specify these conditions separately in the COUNTIFS function.
- Enter the function below into a cell.
=COUNTIFS(C5:C19,">=500",C5:C19,"<=1000")Note: We have specified the same range twice as you can see from the above formula. The COUNTIFS Google Sheets function allows you to apply multiple checks to the same range. This is what makes it possible to check for numbers between 500 and 1000 in our table i.e to Count cells between two numbers in Google Sheets.
- Press Enter.
The COUNTIFS function returns 11 as in the previous example in which we had to sort the data first.
We have succeeded in counting the number of cells between 500 and 1000.
Conclusion
Using the COUNTIFS Google Sheets function we can easily count cells between two numbers by applying multiple criteria to the same data range.
Some related questions
How do I use COUNTIF with two different criteria in Google Sheets?
The COUNTIF function lets you count values based on a single criterion. Counting values based on multiple criteria can be useful for situations such as when you need to count cells between two numbers in Google Sheets. In such cases, use the COUNTIFS Google Sheets function.
E.g
=COUNTIFS(C5:C19,">=500",C5:C19,"<=1000")What does the COUNTA function do?
The COUNTA function simply counts the number of non-empty cells in a range supplied to it. We can use the COUNTA function to count the number of cells between two numbers as shown in this article.
E.g
=COUNTA(C9:C19) See also
Here are some other similar articles on our blog, check them out:
How to Count Non-empty Cells in Google Sheets – 4 Useful Methods
How To Count Unique Values In Google Sheets – 2 Easy Steps
How to Jump to Cell in Google Sheets – 3 Quick and Easy methods