counting values in 'bands' from a list of numbers

  • Thread starter Thread starter Barley Man
  • Start date Start date
B

Barley Man

I am conducting a survey into cars' speeds. I have columns of the speeds
recorded on each day. The number of cells in each column do vary in length
and the last 'rows' of the columns are therefore ikely to be blank and I want
to exclude those. I want to count the numbers of cars in each row that are in
certain speed 'bands', e.g. 0 to 30mph, 31 to 40mph, 41 to 50mph etc.. I
expect to place the results in multiple cells, one for each speed 'band', at
the end of each column. I can use 'countif' and 'and' but I can't seem to
combine them into a single formula.

Example: in column B in rows 3 to 500

B
Speeds
52
38
16
15
etc.
37
26
12
52
<blank>
<blank>
<blank>
<blank>
*******
Totals
11 between 0 and 30
36 between 31 and 40
95 between 41 and 50
etc.

Can that be written to single 'formulae' for each 'total' cell ? If so, how?

Ian
 
actually sumproduct would probably be easier

=sumproduct(($a$2:$a$22>=0)*($a$2:$a$22<=30))
etc
 
Don, thanks for your help but neither of those work for me.

The second one gives something like :-

the total the count of those >0 multiplied by the total count of those <31.

I can't use =countif() properly because I need to 'bracket' the speeds with
a Max and a Min and I can't find the 'grammer' for combining two criteria
something like:-

=countif(a3:a103 (between 0 and 30))

......within a '=countif()' statement.

Ian
 
Correction!
**************

You were correct with the sumproduct!

However, you'd said

"=>0"

.......which had included all those cells which were empty and that's what
had confused me!

Thanks for your help!

Ian
 
Back
Top