nested countif

[FLChick]

Can someone help me with this? I created the following statement but
it's not doing what I want it to do.

=IF(COUNTIF(B2:B81,"no"),COUNTIF(C2:C81,"<>Production Issue"))

I want a count of the number of times "no" is in column B and
"Production Issue" are NOT in column C. The result I'm getting is a
total number of times "Production Issue" is not in column C,
regardless of what is in column B.


For example, I would want a count of 1 if I used the formula on the
data below (only the 3rd row has "no" in column B and does not have
"Production Issue" in column C).

A B C
966 yes
967 yes dupe of 966
968 no Violates BRs
969 no Production Issue
970 no Production Issue

Thanks!

June

 

[Ron Coderre]

Try this:

=SUMPRODUCT((B2:B81="no")*(C2:C81<>"Production Issue"))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

 

[FLChick]

Yes it does, thank you! I have not used the sumproduct before so
didn't even think of it.

 

[Guest]

=COUNTIF(B1:B81, "no")-COUNTIF(C1:C81,"production issue")
You can find this formula in the help under countif formula.

 

[Guest]

=COUNTIF(B1:B81, "no")-COUNTIF(C1:C81,"production issue")
I don't know why the previous doesn't show...

 

[Peo Sjoblom]

Your formula won't work. If for instance there are no "no" in B and 50
"production issue" in C
your formula will return -50. You would need either a classis array formula
or SUMPRODUCT that will compare each row


=SUMPRODUCT(--(B2:B81="no"),--(C2:C81<>"production issue"))


=SUM((B2:B81="no")*(C2:C81<>"production issue"))

entered with ctrl + shift & enter



--
Regards,

Peo Sjoblom