counting values in 'bands' from a list of numbers

[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

 

[Don Guillett]

Look in the help index for COUNTIF

 

[Don Guillett]

actually sumproduct would probably be easier

=sumproduct(($a$2:$a$22>=0)*($a$2:$a$22<=30))
etc

 

[Barley Man]

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

 

[Barley Man]

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

 

[T. Valko]

Try this:

=COUNTIF(A3:A103,">=0")-COUNTIF(A3:A103,">30")

 

[Barley Man]

Nice one, Biff!

Thanks

Try this:

=COUNTIF(A3:A103,">=0")-COUNTIF(A3:A103,">30")

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Nice one, Biff!

Thanks