Count cells that are 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.

	A	B
1	=TEXTBEFORE(B1, "-") = 0	0-20
2	=TEXTAFTER(B1, "-") = 20
3	=TEXTBEFORE(B3, "-") = 21	21-30
4	=TEXTAFTER(B3, "-") = 30
5	=TEXTBEFORE(B5, "-") = 31	31-50
6	=TEXTAFTER(B5, "-") = 50
7	=TEXTBEFORE(B7, "-") = 51	51-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.

	A	B	C	D
1	=COUNTIFS(D1:D8, ">="&B1, D1:D8, "<="&B2) = 1	0	0-20	29
2	=COUNTIFS(D1:D8, ">="&B3, D1:D8, "<="&B4) = 1	20		77
3	=COUNTIFS(D1:D8, ">="&B5, D1:D8, "<="&B6) = 2	21	21-30	64
4	=COUNTIFS(D1:D8, ">="&B7, D1:D8, "<="&B8) = 3	30		19
5		31	31-50	35
6		50		63
7		51	51-70	55
8		70		32

Alternative Formula - LEFT, RIGHT, FIND, LEN

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.

	A	B
1	=LEFT(B1, FIND("-", B1), 1) = 0	0-20
2	=RIGHT(B1, LEN(B1)-FIND("-", B1)) = 20
3	=LEFT(B3, FIND("-", B3), 1) = 21	21-30
4	=RIGHT(B3, LEN(B3)-FIND("-", B3)) = 30
5	=LEFT(B5, FIND("-", B5), 1) = 31	31-50
6	=RIGHT(B5, LEN(B5)-FIND("-", B5)) = 50
7	=LEFT(B7, FIND("-", B7), 1) = 51	51-70
8	=RIGHT(B7, LEN(B7)-FIND("-", B7)) = 70

Alternative Formula - SUM, IF

You can use the SUM function and IF function.
Array Formulas.

Alternative Formula - SUMPRODUCT

You can use the SUMPRODUCT function

Built-in Functions

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

User Defined Function

COUNTBETWEEN - Returns the number of cells that have a value that is between a range.