|
nested countif
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 |
nested countif
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 |
nested countif
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 |
nested countif
=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 |
nested countif
=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 |
nested countif
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 |
| All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com