Number of numbers between two numbers formula

Last updated at March 18, 2019 by

How many whole numbers are there between 3 & 9?

This video is only available for Teachoo black users

Solve all your doubts with Teachoo Black (new monthly pack available now!)

Home ➜ Excel Formulas ➜ Count Between Two Numbers (COUNTIFS)

In Excel, you can count between two numbers using the COUNTIFS function. With the COUNTIFS function, you can specify an upper limit of the numbers, and a lower limit to create a range of numbers to count. In the following example, we have a list of names with age. Now we need to count the people of two ages i.e., two numbers.

sample-file.xlsx

Using COUNTIFS to Count Between two Numbers

You can use the following steps to create formulas using the COUNTIFS to count numbers between 10 and 25.

1. First, enter the “=COUNTIS(“ in cell C1.
   
   
2. After that, refer to the range from where you want to count the values.
   
   
3. Next, you need to specify the upper number using greater than and equal sign.
   
   
4. From here, again you need to refer to the range of numbers in the criteria2.
   
   
5. In the end, use lower than and equal signs to specify number below than 10 in the counting range.
   
   

=COUNTIFS(B2:B26,”<=25″,B2:B26,”>=10″)

COUNTIFS can take multiple criteria and count values based on them, and to understand this formula, we need to split it into two parts.

1. First, you have specified the range from which you want to count the numbers and the upper number using the greater than and equal sign.
2. After that, you specified the lower number and the range of numbers.

sample-file.xlsx

Using SUMPRODUCT to Count Cells Between Two Numbers

You can also use the SUMPRODUCT function for counting the number of cells between two numbers. In this formula, we need to use the INT function along with the SUMPRODUCT.  See the formula below:

=SUMPRODUCT(INT(B2:B26>=10), INT(B2:B26<=25))

To understand the formula, we need to split the formula.

In the first INT function, we have used the range of cells from where we want to count the numbers. And >= signs to only refer to the numbers greater than or equal to 10. And it returns an array showing all the numbers above the 10 using 1.

And in the second INT function, again you have the same range that considers the numbers lower than and equal to 25.

With both INT functions you have two different arrays. In the first, you have 1 for each number that is greater than or equal to 10, and in the second, 1 for the values which are lower than or equal to 25.

In the end,

• SUMPRODUCT multiplies each value from the first array with the value from the second array, and you will get an array of values that represents numbers that are between 10 and 25.
• And after that return the sum of those values from the array.

sample-file.xlsx

⇠ Excel Formulas List

This calculator is created by the user's request /690/

The objective has been formulated as follows:

"Relations between the two numbers A and B:

1. What percentage is A from B and vice versa;
2. What percentage is the difference between A and B relative to A and relative to B;
3. Any other relations between A and B."

So I come up with some relations that are calculated by this simple calculator. The values are expressed as a decimal (as a result of division); we multiply that result by 100 to convert it into a percentage.

Calculation precision

Digits after the decimal point: 2

The file is very large. Browser slowdown may occur during loading and creation.

If you need to count cell numbers that contain specific values between two numbers or dates, the formula of the CountIf function can quickly help you. In this tutorial, we will show you details of counting number of cells that between two values or dates in Excel.

Count cell numbers between two numbers with CountIf function
Count cell numbers between two dates with CountIf function
Count cell numbers between two values or dates with Kutools for Excel (without formula)

Count cell numbers between two numbers with CountIf function

For example, you have a worksheet with the below numbers in the cell B2:B8, and you want to count the cell numbers that between number 75 and 90, please do as follows.

Counting cell numbers > X and < Y

If you want to count cell numbers between 75 and 90 but not including 75 and 90, you need to:

1. Select a blank cell which you want to put the counting result. Copy and paste the formula =COUNTIFS(B2:B8,">75", B2:B8,"<90") into the Formula Bar, and then press the Enter key.

Then you can see the result of cell numbers displaying in the selected cell immediately.

Counting cell numbers >= X and <= Y

For counting cell numbers >=75 and <= 90, please use this formula =COUNTIFS(B2:B8,">=75", B2:B8,"<=90").

1. Select a blank cell for putting the counting result, copy and paste the above formula into the Formula Bar. Then press the Enter key. You will see the counting result showing in the selected cell.

Count cell numbers between two dates with CountIf function

In this section, we are showing you how to count cell numbers between two dates based on cell reference.

For example the date range existing in cell A14:A20 in your worksheet, and you want to count the cell numbers based on cell B21 and B22, please do as follows.

1. Select a blank cell, copy and paste the formula =COUNTIFS(A14:A20,">="&B21,A14:A20,"<="&B22) into the Formula Bar, then press the Enter key. See screenshot below.

Note: After copying and pasting the formula above, you need to change the cell range and the values manually for your own needs.

Count cell numbers between two values or dates with Kutools for Excel (without formula)

If you are a formula newbie and cannot handle formula easily, we recommend you a handy add-in to easily count cell numbers between two values or dates in Excel.

Before applying Kutools for Excel, please download and install it firstly.

Step 1. Click Kutools > Select > Select Specific Cells.

Step 2. In the Select Specific Cells dialog box, select cell range in the Select cells in this range section, select Cell option in the Selection type section, specify your conditions such as Greater than 75 and Less than 90 in the Specific type section, and finally click the Ok button.

Step 3. Then a Select Specific Cells dialog box will pop up to show you the total number of cells which meet the conditions you specified above, click the OK button and the cells are selected immediately.

Note: With this feature, you can also count and select the dates between two dates, you just need to type the two dates into the Select Specific Cells dialog box as following shown:

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

Demo: Count cell numbers between two values or dates with Kutools for Excel

Related articles: