Excel: How to extract the number of rows (or total count) of a specified numeric range from a column where random numbers are being generated?

Question

I need to extract the count of (or the number of rows) a specified numeric range from a column where random numbers are being generated (pasted).

On the screenshot below you can see the random numbers in column A. On the right are specific numeric ranges I need to extract from column A.

Basically, I need to know how many rows fall into each numeric range category.

Appreciate any help in solving this!

Answer 1

Several ways to achieve this, such as using a variety of formulas, or even a pivot table if your ranges are consistent.

Here's one method, where you define a start and end value for your range within their own cells. Then the following formula is in cell E2:

=COUNTIFS(A:A,">="&C2,A:A,"<="&D2)

There's probably a slightly shorter version of this formula, such as combining COUNTIFS with AND.

Here's a pivot table, where we group the value by 10's...

Answer 2

Enter this formula and drag to down :

=SUMPRODUCT(($A$1:$A$26>=NUMBERVALUE((LEFT(C3,FIND(" to ",C3)-1))))*($A$1:$A$26<=NUMBERVALUE(RIGHT(C3,LEN(C3)-LEN(" to ")-FIND(" to ",C3)+1))))