How to Count Between Two Numbers in Excel?

When analyzing data in Excel, we sometimes need to know how many numbers appear between two numbers.

For people who work with large datasets, this is a common task.

Manually counting is time-consuming and error-prone.

Excel has a couple of functions that allow us to easily count the number of cells that contain a value between two given numbers.

In this article, you will learn several methods for counting numbers between two numbers in Excel.

Method 1 – Use COUNTIFS Function to Count Between two Numbers in Excel

To count between two numbers in Excel, we can use the COUNTIFS function.

I have the below data set where column A shows the employee names and column B shows the experience of employees in years.

Now I need to find how many employees with 5 to 10 years of experience.

Dataset to count between two numbers

You can use the below function to find the number of employees with 5 to 10 years of experience.

=COUNTIFS(B2:B11,">=5",B2:B11,"<=10")
COUNTIFS function to count between two numbers

The syntax of the COUNTIFS function is COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…).

For Criteria_range1, we have to select the range where we need to evaluate the 1st criteria. So, we are selecting cells B2 to B11.

Then, we have to enter the criteria for the 1st range. Our 1st criterion is to have years of experience greater than or equal to 5.

So, enter “>=5”. Here, we have entered the criteria within quotes because we have used a logical operator to define the criteria.

Similarly, we can enter multiple criteria ranges with their respective criteria. So, we select cells B2 to B11 as the 2nd criteria range and enter “<=10” as the criteria for the second range. 

Note: When selecting subsequent ranges for criteria, they must have the same number of rows and columns as the 1st criteria range. Otherwise, it will return a #VALUE! Error.

COUNTIFS range needs to be the same
Also read: How to COUNTIF Partial Match in Excel?

Method 2 – Using SUM and IF Functions to Count Between two Numbers in Excel

Another easy way to count between two numbers is to use the combination of SUM and IF functions.

I have the below data set where column A shows the employee names and column B shows the experience of employees in years.

Now I need to find how many employees with 5 to 10 years of experience.

Dataset to count between two numbers

Approach #1 – Multiply two IF functions

You can use the below function to find the number of employees with 5 to 10 years of experience.

=SUM(IF(B2:B11>=5,1,0)*IF(B2:B11<=10,1,0))
SUM and IF function to count between two numbers

The syntax of the IF function is IF(logical_test,[value_if_true],[value_if_false].

Therefore, we are evaluating experience greater than or equal to 5 years with the first IF function.

The value will receive “True” if the experience value is more than or equal to 5, and “False” otherwise. Then, if the logical test result is “True,” we say the function to return 1.

If the logical test returns “False,” we specify that we want to receive “0.”

We are checking the experience of less than or equal to 10 years with the second IF function.

The result will be “True” if the value is less than or equal to 10, and “False” otherwise.

So, if the outcome of the logical test is “True,” we tell the function to return 1. If the logical test returns “False,” we say that we want to receive “0.”

Next, we multiply the corresponding results of two IF functions using the asterisk (*) sign.

This gives an array of numbers where it would be 1 if both corresponding values in both the IF function are 1. Otherwise, we will get 0.

Finally, we use the SUM function to get the total value of the multiplied results.

In this case, the SUM function will help to get the total value of the corresponding multiplied results of the two IF functions.

Approach #2 – Nested IF functions

You can use the below function to find the number of employees with 5 to 10 years of experience.

=SUM(IF(B2:B11>=5,IF(B2:B11<=10,1,0),0))
Nested IF function to count between two numbers

The syntax of the IF function is IF(logical_test,[value_if_true],[value_if_false].

So, the first IF function is used to test whether the experience is greater than or equal to five years.

It will be “True” if the value is more than or equal to 5, and “False” otherwise.

If the outcome of the logical test is “True,” we then provide the function to assess another IF function. If the logical test returns “False,” we specify to get “0.”

We are evaluating whether the experience is less than or equal to 10 years by using the inner IF function.

If the value is less than or equal to 10, the inner IF function will get “True” and otherwise “False”.

Then, if the outcome of the logical test of the inner IF function is “True,” we tell the function to return 1.

If the logical test of the inner IF function returns “False,” we specify to get “0.”

Therefore, we only receive 1 if the logical tests of both IF functions are True. If not, we shall get 0.

Finally, we use the SUM function to get the total value of the results.

In this case, the SUM function will help to get the total value of the results of the nested IF function.

Method 3 – Using SUMPRODUCT Function to Count Between two Numbers in Excel

You can use the SUMPRODUCT function also to count between two numbers in Excel.

I have the below data set where column A shows the employee names and column B shows the experience of employees in years.

Now I need to find how many employees with 5 to 10 years of experience.

Dataset to count between two numbers

You can use the below function to find the number of employees with 5 to 10 years of experience.

=SUMPRODUCT((B2:B11>=5)*(B2:B11<=10))
SUMPRODUCT function to count values between two numbers

The syntax of the SUMPRODUCT function is SUMPRODUCT(array1, [array2], [array3], …).

Now we will check how we have used this function for our calculation.

The first expression (B2:B11>=5) is a logical test. It will check whether or not the values are greater than or equal to 5.

The formula will return “True” if the value is greater than or equal to 5, and “False” otherwise.

The second expression (B2:B11<=10) is also a logical test. It will evaluate whether or not the values are less than or equal to 10.

The formula will return “True” if the value is less than or equal to 10, and “False” otherwise.

The asterisk sign (*) in between the two logical expressions will convert True and False values to 1’s and 0’s and multiply the corresponding values.

When two related numbers are multiplied, the formula returns 1 if both corresponding values are 1, otherwise, it returns 0. 

Finally, we use the SUMPRODUCT function to get the sum of the above-multiplied results.

Method 4 – Using SUM Function to Count Between two Numbers in Excel

You can also use the SUM function to count between two numbers in Excel.

I have the below data set where column A shows the employee names and column B shows the experience of employees in years.

Now I need to find how many employees with 5 to 10 years of experience.

Dataset to count between two numbers

You can use the below function to find the number of employees with 5 to 10 years of experience.

=SUM((B2:B11>=5)*(B2:B11<=10))
SUM function to count between two numbers

The syntax of the SUM function is SUM(number1,[number2],…).

Now we will check how we have used this function for our calculation.

The first expression (B2:B11>=5) is a logical test. It will check whether or not the values are greater than or equal to 5.

The formula will return “True” if the value is greater than or equal to 5, and “False” otherwise.

The second expression (B2:B11<=10) is also a logical test.

It will evaluate whether or not the values are less than or equal to 10. The formula will return “True” if the value is less than or equal to 10, and “False” otherwise.

The asterisk sign (*) in between the two logical expressions will convert True and False values to 1’s and 0’s and multiply the corresponding values.

When two related numbers are multiplied, the formula returns 1 if both corresponding values are 1, otherwise, it returns 0. 

Finally, we use the SUM function to get the sum of the above-multiplied results.

In this lesson, you learned how to simply count the number of cells that have a value between two provided numbers.

You are free to use any of the techniques that suit your needs.

Using such functions to count numbers between two numbers will make your calculations faster and more accurate.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment