coutif with 3 criteria including brackets

[Ray]

Hello

I try to to use 3 critéria in a formula with the Countif function. Actually
two of them represent a bracket.
For the calcul of the bracket I found the good result by using :
=countif(E4:E2337;"<=2")-countif(E4:E2337;"<=5")
where I am in trouble is when I try to add the third criterion in my formula
=countif(D42337;"yes")
I tried those math operators but none of them works +, -, *

Do you have an idea ?
Thanks
Ray

 

[Bob Phillips]

Ray,

I am not quite sure what you were doing, but is this what you want


=SUMPRODUCT(--(E4:E2337>=2);--(E4:E2337<=5);--(D42337="yes"))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ray]

Thanks for your answer Bob,
I am going to try this formula right away .
One thing, I don't understand why you used the "--" in the formula.
Could you tell me please ,
Thanks

 

[Ray]

Hello Bob
I have just tried this formula and it works fine
Thank you very much.
But I still don't see why you use the "--" in the formula
Thanks in deed
Ray

 

[Lewis Clark]

The tests in the parentheses return TRUE or FALSE. The "--" makes Excel
turn true/false into 1/0, which allows doing the math.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ray]

Thanks for this explaination Lewis
Ray

The tests in the parentheses return TRUE or FALSE. The "--" makes Excel
turn true/false into 1/0, which allows doing the math.