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,"<Product ion 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

Try this:

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

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

Regards,

Ron
Microsoft MVP (Excel)



"FLChick" wrote in message
ups.com...
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,"<Product ion 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

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

On Sep 4, 9:58 am, "Ron Coderre"
wrote:
Try this:

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

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

Regards,

Ron
Microsoft MVP (Excel)

"FLChick" wrote in message

ups.com...

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,"<Product ion 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

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

"FLChick" wrote:

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

On Sep 4, 9:58 am, "Ron Coderre"
wrote:
Try this:

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

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

Regards,

Ron
Microsoft MVP (Excel)

"FLChick" wrote in message

ups.com...

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,"<Product ion 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

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






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

"FLChick" wrote:

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

On Sep 4, 9:58 am, "Ron Coderre"
wrote:
Try this:

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

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

Regards,

Ron
Microsoft MVP (Excel)

"FLChick" wrote in message

ups.com...

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,"<Product ion 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

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

"FLChick" wrote:

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,"<Product ion 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