I am using Excel 2000. And, I need to count cells that
fall in multiple criteria. For example, I have 5 numbers
93.84, 100, 95, 92.5, and 92.5 in cells from A6 to A10. I
wrote the following formula to count the number of the
cells that have numbers between 91 and 95, inclusively:

=SUM(((A6:A10)>90)*((A6:A10)<=95))

Tha should return 4; however, I get an error messge
(#VALUE!). Is that a right formula to count the cells
that I want?

Actually, the formula I wrote in the previous posting was
different than I have. The one I have is as follows:

=SUM(IF(((A6:A10)>90)*((A6:A10)<=95),1,0))

Thanks.

>-----Original Message-----
>I am using Excel 2000. And, I need to count cells that
>fall in multiple criteria. For example, I have 5 numbers
>93.84, 100, 95, 92.5, and 92.5 in cells from A6 to A10.
I
>wrote the following formula to count the number of the
>cells that have numbers between 91 and 95, inclusively:
>
>=SUM(((A6:A10)>90)*((A6:A10)<=95))
>
>Tha should return 4; however, I get an error messge
>(#VALUE!). Is that a right formula to count the cells
>that I want?
>.
>

When you have more that one criteria, it is time to call upon SUMPRODUCT
=SUMPRODUCT(--(A6:A10>90), --(A6:A10<=95)

But for a simple count, you could also use
=COUNTIF(A6:A10,">90")-COUNTIF(A6:A10,">95")

Best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"Atchy" <(E-Mail Removed)> wrote in message
news:a08f01c43475$b05190d0$(E-Mail Removed)...
> Actually, the formula I wrote in the previous posting was
> different than I have. The one I have is as follows:
>
> =SUM(IF(((A6:A10)>90)*((A6:A10)<=95),1,0))
>
> Thanks.
>
> >-----Original Message-----
> >I am using Excel 2000. And, I need to count cells that
> >fall in multiple criteria. For example, I have 5 numbers
> >93.84, 100, 95, 92.5, and 92.5 in cells from A6 to A10.
> I
> >wrote the following formula to count the number of the
> >cells that have numbers between 91 and 95, inclusively:
> >
> >=SUM(((A6:A10)>90)*((A6:A10)<=95))
> >
> >Tha should return 4; however, I get an error messge
> >(#VALUE!). Is that a right formula to count the cells
> >that I want?
> >.
> >