Excel has multiple count-related functions that allow you to quickly analyze large data sets.
A common task for many people Is to count cells that contain a value in between two specific numbers.
For example, if you are analyzing sales data for different Sales Reps in your company, you may want to know the number of people whose sales are between $100K and $500K.
In this tutorial, I will show you a couple of different methods you can use to count between two numbers in Excel using formulas such as COUNTIF, COUNTIFS, and SUM.
Click here to download the example Excel file
COUNTIFS Formula to Count Between Two Numbers
The easiest way to count between two numbers is by using the COUNTIFS function. This function is available in Excel 2010 and higher versions.
Let me show you how it works.
Below I have a data set where I have student names in column A and their scores in column B, and I want to know the number of students who have scored more than 35 but less than 75 (i.e., between 35 and 75).
Below is the COUNTIFS formula that will do this:
=COUNTIFS(B2:B15,">35",B2:B15,"<75")
The syntax of the COUNTIFS function is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
In our formula, the COUNTIFS function takes four arguments:
- criteria_range1 – This is the range that has the numbers
- criteria1 – This is the first criterion (“>35”), that the scores should be greater than 35
- criteria_range2 – This is again the same range that has the numbers
- criteria2 – This is the second criterion (“<75”), that the scores should be less than 75
COUNTIFS function gives us only that count that satisfies both pairs of conditions (i.e., numbers that are more than 35 and less than 75)
Note: Make sure that your criteria are always in double quotes (as in “<35”). Also, in this case, we excluded counting the cells that have the values 35 and 75. In case you want them to be counted, you can use “<=35” and “<=75” as the criteria.
In case you’re using an older version of Excel that does not have the COUNTIFS function, or you need to share your file with someone who’s working on an older version, you can use the next two methods I’ve covered in this article.
Also read: Check IF a Date is Between Two Given Dates in Excel
COUNTIF Formula to Count Between Two Numbers
Below I have the same data set where I have the students’ names in column A and the scores in column B, and I want to find out the total number of students who have scored between 35 and 75.
Here is the COUNTIF formula that will give us the result:
=COUNTIF(B2:B15,">35")-COUNTIF(B2:B15,">75")
The above formula uses two COUNTIF functions:
- The first COUNTIF function gives us the count of all the cells where the score is more than 35. These would also include cells where the score is more than 75
- The second COUNTIF function gives us the count of only those cells where the value is more than 75
Subtracting the value we get from the second COUNTIF from the value that we get from the first COUNTIF would give us the right result.
Click here to download the example Excel file
Also read: SUM Values Between Two Dates (using SUMIFS formula)
SUM Formula to Count Between Two Numbers
While in most cases, you’re better off using the COUNTIFS function or the COUNTIF function, let me show you another smart way to use a simple SUM formula to count between two numbers.
The good thing about this method is that it is going to work in every version of Excel.
Below I have the same data set where I want to calculate the number of cells with scores between 35 and 75.
Here is the SUM formula that will do this for us:
=SUM((B2:B15>35)-(B2:B15>75))
The above formula uses (B2:B15>35) to get an array of TRUE and FALSE where we would get a TRUE in case the score in the cell is more than 35, and FALSE in case the score is less than or equal to 35.
In the back end, Excel considers TRUE as 1 and False as 0, so when we compute (B2:B15>35)-(B2:B15>75), it gives us an array of TRUEs and FALSEs (or 1s and 0s), where we would get 1 only if the cell has a value that is more than 35 and less than 75.
This array is then wrapped up within the SUM function that simply counts the total number of 1s and gives us the result.
Note: In case you’re using an older version of Excel that does not have dynamic arrays when you have entered the formula, you should use Control + Shift + Enter instead of Enter (i.e., hold the Control and the Shift key and then press the Enter key)
So these are three simple formulas that you can use to count between two numbers in Excel.
The easiest way would be to use the COUNTIFS function. But in case you do not have it or you don’t want to use it because of compatibility reasons, you can also use the COUNTIF or the SUM function method.
I hope you found this Excel tutorial useful.
Other Excel articles you may also like:
- Calculate the Number of Months Between Two Dates in Excel
- Count Unique Values in Excel Using COUNTIF Function
- Count Cells Less than a Value in Excel (COUNTIF Less Than)
- How to Use Multiple Criteria in Excel COUNTIF and COUNTIFS Function
- How to Count Cells that Contain Text Strings
- How to Count Filtered Rows in Excel?
