Multiple countif criteria

  • Thread starter Thread starter hotherps
  • Start date Start date
H

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
 
getting a #value error. I look at that site and entered the formul
pressing ctrl, shift and enter

still did not wor
 
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
 
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
 
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
 
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
 
Hi,

hotherps > said:
According to that site if you want to count both conditions and add them
together you can use your formula.
Click to expand...

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,
Click to expand...

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

Regards,

Daniel M.
 
Back
Top