Conditional function with count

[Guest]

Hi,

I tried to make a formula that count if two statements are true. For example,
=COUNTIF(M!E2:E37,"P") This formula give me the correct number that is 7.
=COUNTIF(M!L2:L37,"Y") This formula give me the correct number that is
25.

Then, I tried to combined this 2 formulas for using a conditional statement
AND, because I want that the formula count when this 2 statements are true.
=AND((COUNTIF(M!E2:E37, "P")), (COUNTIF(M!L2:L37, "Y"))) Result: True

Then, I tried to make the count if this condition are True, but the count
give a wrong number. The formula that I tried is
=COUNT(AND((COUNTIF(M!E2:E37, "P")), (COUNTIF(M!L2:L37, "Y"))))

Somebody can help me.

Thanks

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(M!E2:E37={"P,"Y"}))

See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of
the "--"

 

[Guest]

You're using the COUNT function, which counts numbers, not logical values.
To coerce the COUNTIF function into returning 1s for TRUE values and 0 for
FALSE values, which, in turn, will allow you to use the COUNT function, try:

=COUNT(AND(--(COUNTIF(M!E2:E37, "P")),--(COUNTIF(M!L2:L37, "Y"))))

The -- symbol coerces logicals (TRUE and FALSE) into 1 and 0 respectively.

Dave

 

[Alan]

Try
=SUMPRODUCT(--(M!E2:E37="P"),--(M!L2:L37="Y"))
Regards,
Alan.

 

[Guest]

Thanks for your help. It works.

Try
=SUMPRODUCT(--(M!E2:E37="P"),--(M!L2:L37="Y"))
Regards,
Alan.

 

[David Biddulph]

Your first COUNTIF returns 7; 7 is a non-zero number and hence is regarded
as TRUE. Similarly your second COUNTIF would be regarded as TRUE.
Your AND condition is ANDing two TRUEs and thus gives TRUE.
Your final expression is effectively =COUNT(AND(TRUE,TRUE)), so I would
expect it to return 1.

 

[Guest]

Xl 2007

=COUNTIFS(M!E2:E37,"P",M!L2:L37,"Y")