Google Sheets Count Cells Between Two Numbers with COUNTIF Function

We often need to know the specific amount of a particular type of number within a range. Like most schools publish their results not showing the marks but showing the grade points. And this grading system is usually classified like- 80~100% is equivalent to “A”, 70~79% is “B” etc. So it is important for them to calculate the number of total students with grades A, B, C or all. In this article, we have tried to demonstrate some easy methods on how to COUNTIF between two numbers in Google Sheets.


A Sample of Practice Spreadsheet

You can download the Google Sheets used to demonstrate methods in this article from here.


2 Easy Methods to Count Cells Between Two Numbers with COUNTIF Function in Google Sheets

Now let me show you two easy ways to COUNTIF between two numbers in Google Sheets.


1. Applying Single COUNTIF Function

We have a dataset of products with their corresponding prices here. What we want is to count available products whose prices are greater than a specific value. Suppose, we want to count available products whose prices are greater than “€300”.

Steps:

  • In the following dataset, select Cell C16, apply the following formula and press Enter-
=COUNTIF(C5:C13,">="&C15)

The output we will get is “0” as no such entry is in Cell C15.

  • Now, select Cell C15, type “300” and press Enter-

You can see that the total number of available products whose prices are greater than “€300” is “5”.

Read More: Difference Between COUNT and COUNTA in Google Sheets


2. Merging Multiple COUNTIF Functions

Suppose, we have a dataset of the Time Schedule of TV Shows on HBO. What we want to know is the number of TV shows within a specific time range. Let the time range be 4:30 PM to 8:00 PM.

Steps:

  • In the following dataset, select Cell C17, apply the following formula, and press Enter-
=COUNTIF(C5:C13,">"&C15)-COUNTIF(C5:C13,">"&C16)

We got the output as “0” as there are no such entries in Cell C15 and Cell C16.

Formula Breakdown

  • COUNTIF(C5:C13,”>”&C15)

Here, the function counts the total number of available values that are greater than the time in Cell C15.

  • COUNTIF(C5:C13,”>”&C16)

And here the function counts the total number of available values that are greater than the time in Cell C16.

  • COUNTIF(C5:C13,”>”&C15)-COUNTIF(C5:C13,”>”&C16)

What this combination will do is 1st function result minus 2nd function result. And the difference will give us the number of shows between the times.

  • Now, select Cell C15 then type “4:30 PM” and press Then in Cell C16 type “8:00 PM” and press Enter

You can see that the output “No. of Shows” is “5”.

Read More: How to Calculate Hours Between Two Times in Google Sheets


4 Alternatives to Count Cells Between Two Numbers in Google Sheets

The COUNTIF function has limitations because it can’t deal with multiple criteria, so if we take the help of some other functions then there are a lot of ways to COUNTIF between two numbers in Google Sheets. Here, I’ll show you 4 useful alternatives among them.

We will use the following sample dataset to describe these methods accurately. The dataset represents some students’ scores on a test.

Google Sheets COUNTIF Between Two Numbers


1. Employing COUNTIFS Function

The COUNTIFS function returns the number of numeric values under multiple criteria. Like in the following example shown below, this function is used to meet two criteria, and which are to count numbers greater than or equal to 80 and less than or equal to 100.

Steps:

  • Suppose, in the following dataset, we want to know the total number of students with scores between 80 and 100.

Employing COUNTIFS Function to countif between two numbers in google sheets

  • Select Cell C18, apply the following formula and press Enter-
=COUNTIFS(C8:C15,">=80",C8:C15,"<=100")

And soon after you will get the total number of counts like the image below.

Read More: How to Use IF Condition Between Two Numbers in Google Sheets


Similar Readings


2. Combining SUMPRODUCT and INT Functions

The INT function in Google Sheets finds the integer portion of numbers of a particular range of cells within a given criterion. The SUMPRODUCT function shows the result meeting all the criteria given in this function.

Steps:

  • Let’s assume, in the following dataset, we want to calculate the total number of students with scores 80 to 100.

2. Combining SUMPRODUCT and INT Formula to countif between two numbers in google sheets

  • Select Cell C18, apply the following formula and press Enter
=SUMPRODUCT(INT(C5:C15>=80),INT(C5:C15<=100))

Then have a look, we got the same output as the previous method.

Formula Breakdown

  • INT(C5:C15>=80),INT(C5:C15<=100)

As this function works under a single criterion, we have used this function twice, the first one working under a criterion that is finding numbers greater than or equal to 80 and the second one working under criterion that is finding numbers less than or equal to 100.

  • SUMPRODUCT(INT(C5:C15>=80),INT(C5:C15<=100))

The SUMPRODUCT function then returns the total number of values that meets both of the criteria.


3. Inserting COUNTUNIQUEIFS Function

The COUNTUNIQUEIFS function returns the unique value of numbers based on multiple criteria.

Steps:

  • Presume, in the below dataset, we want to get the total number of students with scores 80 to 100.

Inserting COUNTUNIQUEIFS Function to countif between two values in google sheets

  • Select Cell C18 and apply the following formula-
=COUNTUNIQUEIFS(C5:C15,C5:C15,">=80",C5:C15,"<=100")

Here we can see that the result is showing “4” but the numbers between 80 and 100 are more than that. It’s because the function of the formula COUNTUNIQUEIFS is to return only unique values of the given criteria.

Read More: How to Find Unique Values Between 2 Columns in Google Sheets


4. Using Filter Option and Drop Down Menu

We can do the same without using any formula and using the built-in Filter option from the toolbar and the Drop Down menu that appears at the bottom right of the sheet after selecting a particular range of cells.

Steps:

  • In the following dataset, select Cell range C4:C15 and create a Filter function from the toolbar as follows-

Using Filter Option and Drop Down Menu to countif between two numbers in google sheets

  • Selecting that option, the Filter option will be created for that column from the first selected cell. Now, assume that we want to get all the values between 80 and 100. Press on the red-marked Filter Icon in Cell C4.

  • A small window will pop up on your screen. Select Filter by condition and then the Condition box.

Using Filter Option and Drop Down Menu to countif between two numbers in google sheets

  • Select Is between.

  • Give input 80 in the first Value or Formula box and 100 in the second Value or Formula box and then select Ok.

Using Filter Option and Drop Down Menu to countif between two numbers in google sheets

  • This will show only the values between 80 and 100.

  • Now select Cell range C8:C15 and then at the bottom right of the sheet select the Drop Down menu.

Using Filter Option and Drop Down Menu to countif between two numbers in google sheets

  • At the Drop Down menu, you will find the total number of cells containing numbers between 80 and 100, and it is mentioned as Count Numbers.


Conclusion

We often need to classify based on numbers or scores and then comes the job to count the total numbers of each class. Meanwhile, in the following article, we have illustrated 4 easiest ways to COUNTIF between two numbers in Google Sheets. Hope this section will surely help you with your work. Visit our site officewheel.com to find more related articles that will help you to become more efficient and expert in Google Sheets.


Related Articles

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo