Multiple countif criteria

[hotherps]

Can you nest multiple conditions in a countif statement?

I have this

=COUNTIF($G$11:$N$298,".")

but the following condition has to be true also:

CZ11 = "x"

I tried this:

=COUNTIF(And($G$11:$N$298,".", CZ11="x"))

Won't work
Thank

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--($G$11:$N$298="."),--($CZ$11:$CZ$298="x"))

For more about this see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[hotherps]

getting a #value error. I look at that site and entered the formul
pressing ctrl, shift and enter

still did not wor

 

[Frank Kabel]

Hi
first no need for CTRL+SHIFT+ENTER
Second: what is the exact formula you have used? Please post it

Also you may check that your range does not contain errors

 

[hotherps]

After reading that link you posted a little more I tried this:

{=SUM(IF(--($G$11:$N$15="."),--($CZ$11:$CZ$15="x")))}

and it worked perfect

Thanks

 

[Frank Kabel]

Hi
but then the following should work also
=SUMPRODUCT(--($G$11:$N$15="."),--($CZ$11:$CZ$15="x"))

No need for array entry. This formula should be a little more efficient

 

[hotherps]

According to that site if you want to count both conditions and add the
together you can use your formula.

When you change it to the way I have it it will only sum the two i
both conditions are true, which is what I need and probably did no
explan myself well enough in my post.

Thanks again Fran

 

[Daniel.M]

Hi,

According to that site if you want to count both conditions and add them
together you can use your formula.

No. If this is what the site says, it's plain wrong.

When you change it to the way I have it it will only sum the two if
both conditions are true,

This is exactly what will occur if you are SUMPRODUCTing two matrices of 1 and 0
(as in Frank's formula).

Regards,

Daniel M.

 

[Frank Kabel]

Hi,



No. If this is what the site says, it's plain wrong.

Hi Daniel
no it does not say that )
Frank