Conditional function with count
 

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

Conditional function with count
 

One way:

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

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



In article ,
anamarie30 wrote:

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

Conditional function with count
 

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

--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"anamarie30" wrote:

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

Conditional function with count
 

Try
=SUMPRODUCT(--(M!E2:E37="P"),--(M!L2:L37="Y"))
Regards,
Alan.
"anamarie30" wrote in message
...
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

Conditional function with count
 

Thanks for your help. It works.

"Alan" wrote:

Try
=SUMPRODUCT(--(M!E2:E37="P"),--(M!L2:L37="Y"))
Regards,
Alan.
"anamarie30" wrote in message
...
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

Conditional function with count
 

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.
--
David Biddulph

"anamarie30" wrote in message
...
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

Conditional function with count
 

Xl 2007

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


"anamarie30" wrote:

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