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

[old grey whiskers]

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

[Ashish Mathur]

Hi,

Assuming the height range is in C1:C9.

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

=COUNT(IF((C5:C9>2)*(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

[Trevor Shuttleworth]

One way:

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

Regards

Trevor


"old grey whiskers" <oldgreywhiskers@discussions.microsoft.com> wrote in
message news:F452DE76-FD3F-444C-9908-88A78C9B039E@microsoft.com...
> 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

[Aladin Akyurek]

=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?

[old grey whiskers]

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" <oldgreywhiskers@discussions.microsoft.com> wrote in
> message news:F452DE76-FD3F-444C-9908-88A78C9B039E@microsoft.com...
> > 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
>
>
>

[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:C9>2)*(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

[Trevor Shuttleworth]

And another option for the collection ...

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

Regards

Trevor


"old grey whiskers" <oldgreywhiskers@discussions.microsoft.com> wrote in
message news:F452DE76-FD3F-444C-9908-88A78C9B039E@microsoft.com...
> 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