Cuontif and AND

  • Thread starter Thread starter Pascale
  • Start date Start date
P

Pascale

This is the formula I am currently using:

COUNTIF(Complaints!$AI$2:$AI$349,("<=4")

I would like it to check >=2 and <=4, but I keep getting errors.

Please help, thanks in advanc
 
=SUMPRODUCT(--(Complaints!$AI$2:$AI$349>=2),--(Complaints!$AI$2:$AI$349<=4))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Can you please explain why you use sumproduct and not countif. Th
formula worked which is fantastic but I am not sure of the applicatio
of sumproduct.... and also what does the -- mean in the formula

Thanks agai
 
Using countif will be faster than using Sumproduct. Bob used sumproduct
because it directly reflects the range you want and accepts multiple
criteria - it is essentially an array formula. The double negative coerces
the boolean results retuned into either 0 or 1.

Countif does not accept multiple criteria, but if you count all the items >=
2 and then count and subtract the number of items >4, you will get the
number you asked for.
 
Back
Top