Cuontif and AND

[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

 

[Bob Phillips]

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

 

[Tom Ogilvy]

=countif(Complaints!$AI$2:$AI$349,">=2")-countif(Complaints!$AI$2:$AI$349,">
4")

 

[Pascale]

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

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

Pascale,

There is an explanation of SUMPROPDUCT at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

--

HTH

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