How can I count values greater than and less than certain values?
 

How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers

Hi,

Assuming the height range is in C1:C9.

Now array enter (Ctrl+Shift+Enter) the following formula in cell C11

=COUNT(IF((C5:C92)*(C5:C9<5),C5:C9))

Regards,


"old grey whiskers" wrote:

How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers

One way:

=COUNTIF(A:A,"=170")-COUNTIF(A:A,"175")

Regards

Trevor


"old grey whiskers" wrote in
message ...
How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel
to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers

=COUNTIF(AgeRange,"="&170)-COUNTIF(Range,""&175)

for an inclusive count.

=COUNTIF(AgeRange,""&170)-COUNTIF(Range,"="&175)

for an exclusive count.

old grey whiskers wrote:
How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?

thanks Trevor that's a great help.
--
old grey whiskers


"Trevor Shuttleworth" wrote:

One way:

=COUNTIF(A:A,"=170")-COUNTIF(A:A,"175")

Regards

Trevor


"old grey whiskers" wrote in
message ...
How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel
to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers

thanks Ashish that's a great help
--
old grey whiskers


"Ashish Mathur" wrote:

Hi,

Assuming the height range is in C1:C9.

Now array enter (Ctrl+Shift+Enter) the following formula in cell C11

=COUNT(IF((C5:C92)*(C5:C9<5),C5:C9))

Regards,


"old grey whiskers" wrote:

How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers

And another option for the collection ...

=SUMPRODUCT((A1:A500=170)*(A1:A500<=175))

Regards

Trevor


"old grey whiskers" wrote in
message ...
How can I count occurances in a range of values. Eg If I have a class of
students ranging from 150cm to 190cm height, what is the formula in Excel
to
count how many people are between 170 and 175 cm. How can I count values
greater than and less than in one operation?
--
old grey whiskers