Formula to COUNTIFS criteria in one range matches but does not match in 2 other ranges

Constance Fetter

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?

jmyzk_cloudsmart_jp

Hi @Constance Fetter

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.

https://app.smartsheet.com/b/publish?EQBCT=3aee7b7a498544c6ab7219212c846004