# 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)

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!