Counting cells using multiple criteria

  • Thread starter Thread starter Atchy
  • Start date Start date
A

Atchy

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.
 
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
 
Back
Top