Count cells that contain numbers in a range

You can display the number of cells that are in a particular range by using the COUNTIFS function.
This function uses two criteria to satisfy two conditions.
The number must be greater than or equal to the smallest number.
The number must be less than or equal to the largest number.


Example

Lets assume the ranges have been entered into a column.
The range has been entered with a dash in between the smallest number and the largest number.
The first step is to extract the smallest and largest numbers from these values.
The smallest number can be obtained using the TEXTBEFORE function.
The largest number can be obtained using the TEXTAFTER function.

 AB
1=TEXTBEFORE(B1,"-") = 00-20
2=TEXTAFTER(B1,"-") = 20 
3=TEXTBEFORE(B3,"-") = 2121-30
4=TEXTAFTER(B3,"-") = 30 
5=TEXTBEFORE(B5,"-") = 3131-50
6=TEXTAFTER(B5,"-") = 50 
7=TEXTBEFORE(B7,"-") = 5151-70
8=TEXTAFTER(B7,"-") = 70 

Once we have the smallest and largest values we can use these numbers to create our criteria.
The first criteria is any value greater than or equal to the smallest number.
The second criteria is any value less than or equal to the largest number.

 ABCD
1=COUNTIFS(D1:D8,">="&B1,D1:D8,"<="&B2) = 100-2029
2=COUNTIFS(D1:D8,">="&B3,D1:D8,"<="&B4) = 120 77
3=COUNTIFS(D1:D8,">="&B5,D1:D8,"<="&B6) = 22121-3064
4=COUNTIFS(D1:D8,">="&B7,D1:D8,"<="&B8) = 330 19
5 3131-5035
6 50 63
7 5151-7055
8 70 32

Alternative Formulas

The COUNTIFS function was added in Excel 2007.
Before Excel 2007 there were two ways to perform conditional counting.
One was using the SUMPRODUCT function and the other was using Array Formulas.
The TEXTBEFORE and TEXTAFTER functions were added in Excel 365.
Before Excel 365 you can use these formulas to get the smallest and largest numbers.
The smallest number could be obtained using the LEFT and FIND functions.
The largest number could be obtained using the RIGHT and LEN functions.

 AB
1=LEFT(B1,FIND("-",B1)-1) = 00-20
2=RIGHT(B1,LEN(B1)-FIND("-",B1)) = 20 
3=LEFT(B3,FIND("-",B3)-1) = 2121-30
4=RIGHT(B3,LEN(B3)-FIND("-",B3)) = 30 
5=LEFT(B5,FIND("-",B5)-1) = 3131-50
6=RIGHT(B5,LEN(B5)-FIND("-",B5)) = 50 
7=LEFT(B7,FIND("-",B7)-1) = 5151-70
8=RIGHT(B7,LEN(B7)-FIND("-",B7)) = 70 

Built-in Functions

COUNTIFS - The number of numerical values in a range that satisfies multiple conditions.
TEXTBEFORE - (Microsoft 365) The characters from the start of a text string before a delimiter.
TEXTAFTER - (Microsoft 365) The characters from the end of a text string after a delimiter.


Related Formula

Count cells that contain numbers between 2 numbers
Count cells that contains dates between 2 dates
Count cells that contain dates in a particular year


© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited Top