counting items in range

[Jennifer]

I need to count the number of items that are within a given data range

Example
Cell: A1 A2 A3 A4 A5 A
Number: 47 22 42 35 49 5

I need to count how many numbers are <50 and >30, which is

Is there anyway to do this? I thought about doing an AND function and then use a CountIf function to count the number of TRUES, but I thought there would be a better way. I have about 200 + numbers I have to go through to see how many lay in what ranges

Thanks for your help in Advance!

 

[Peo Sjoblom]

=COUNTIF(Range,">30")-COUNTIF(Range,">=50")

or

=SUMPRODUCT(--(Range>30),--(Range<50))

--

Regards,

Peo Sjoblom

I need to count the number of items that are within a given data range.

Example:
Cell: A1 A2 A3 A4 A5 A6
Number: 47 22 42 35 49 53

I need to count how many numbers are <50 and >30, which is 4

Is there anyway to do this? I thought about doing an AND function and

then use a CountIf function to count the number of TRUES, but I thought
there would be a better way. I have about 200 + numbers I have to go
through to see how many lay in what ranges.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A300>30),--(A1:A300<50))

 

[Jennifer]

Thanks for you help, I wouldn't of figured that out!

 

[Jennifer]

For future reference

on the sumproduct function, what does '--' represent? Is that another way to do an array? Thanks.

 

[Peo Sjoblom]

It will force the Boolean values (TRUE,FALSE) into either 1s or 0s,
then you can use the built in construction of the SUMPRODUCT


--

Regards,

Peo Sjoblom

For future reference,

on the sumproduct function, what does '--' represent? Is that another way

to do an array? Thanks.