Formula to COUNTIFS criteria in one range matches but does not match in 2 other ranges
Hello:
I am trying to write a formula which will count the number of cells in a range that return "No Match", but only if the cells in 2 other ranges DO NOT contain "No Match". In this case, I want to count the number of assets unmatched ONLY by S/N, excluding them if they contain "No Match" in 2 other ranges. I have tried using variations of the following without success:
=AND(COUNTIF({2023 Assets - True Up for 2024 MATCH SN}, "No Match"), NOT({2023 Assets - MATCH REV_ID} = "No Match"), NOT({2023 Assets - MATCH Maximo} = "No Match"))
OR
=COUNTIFS({2023 Assets - True Up for 2024 MATCH SN}, "No Match"), AND(NOT({2023 Assets - MATCH REV_ID} = "No Match"), NOT({2023 Assets - MATCH Maximo} = "No Match")))
It givers me an error, as have all other formulas I have tried. Is anyone able to help?
Constance Fetter (she/her/elle)
Best Answer
-
Try something like this:😀
=COUNTIFS({2023 Assets - True Up for 2024 MATCH SN}, "No Match", {2023 Assets - MATCH REV_ID}, NOT(@cell = "No Match"), {2023 Assets - MATCH Maximo}, NOT(@cell = "No Match"))
My demo below uses the Sheet Summary fields, so the range reference is different, but the formula structure is the same.
Answers
-
Try something like this:😀
=COUNTIFS({2023 Assets - True Up for 2024 MATCH SN}, "No Match", {2023 Assets - MATCH REV_ID}, NOT(@cell = "No Match"), {2023 Assets - MATCH Maximo}, NOT(@cell = "No Match"))
My demo below uses the Sheet Summary fields, so the range reference is different, but the formula structure is the same.
-
Thank you! That worked perfectly. Much appreciated!
Constance Fetter (she/her/elle)
-
Happy to help.😁
Help Article Resources
Categories
Check out the Formula Handbook template!
