Count Cells Between Two Numbers in Excel and Google Sheets
Written by
Reviewed by
Download the example workbook
In this tutorial, we will demonstrate how to count cells between two numbers.
Count Cells Between two Numbers with COUNTIFS
The COUNTIFS Function counts cells that meet multiple conditions. This example will count cells with test scores between 60 and 75:
=COUNTIFS(C3:C12, ">="&60, C3:C12, "<="&75)
Notice that we must put greater than or equal to and a less than or equal to signs in quotations for the criteria.
The COUNTIFS function also allows you to add further conditions. Using the same example above, let’s say we want to count the number of scores between 60 and 75 in Math.
=COUNTIFS(C3:C12, ">="&60, C3:C12, "<="&75, D3:D12, "Math")
Count Cells Between two Numbers with SUMPRODUCT
You can also use the function SUMPRODUCT to do something similar. The SUMPRODUCT Function is slightly more customizable and is mostly suited for those that are used to Boolean expressions.
Using the same example as above, let’s say we want to count the number of scores between 60 and 75.
=SUMPRODUCT(INT(C3:C12>=60), INT(C3:C12<=75))
Let’s break this formula down to understand it.
The portion of the formula that says C3:C12>=60 or C3:C12<=75 gives us a TRUE or FALSE result. If the result is TRUE, that means it meets the condition.
The INT Function converts a TRUE value to 1 and a FALSE value to 0.
The SUMPRODUCT function then adds together all the 1 (or TRUE) values which gives us the same result as the COUNTIFS function.
Count cells between two numbers in Google Sheets
These function work the same way in Google Sheets.
=COUNTIFS(C3:C12, ">="&60, C3:C12, "<="&75)
=SUMPRODUCT(INT(C3:C12>=60), INT(C3:C12<=75))
