How to use Smartsheet's COUNTIFS function to count the number of cells that match the criteria in one range but not the other two ranges (Community)

table of contents

first

Smartsheet's COUNTIFS function counts the number of cells that meet multiple conditions.But what if you want to count the number of cells that match the conditions in one range, but not the conditions in the other two ranges?

Question summary

Constance Fetter tried to count the number of assets with mismatched S/N using the following formula:

=AND(COUNTIF({2023 assets – MATCH REV_ID} = “No match”), NOT({2023 assets – MATCH Maximo} = “No chance”))

or sending us a message on

=COUNTIFS({2023 Assets – MATCH REV_ID} = “No Match”), NOT({2023 Assets – MATCH Maximo} = “No Chance”)))

However, these expressions resulted in an error.

Answer summary

To solve this problem, use the following formula:

=COUNTIFS({2023 Assets – Match 2024 MATCH SN}, “No Match”, {2023 Assets – MATCH REV_ID}, NOT(@cell = “No Match”), {2023 Assets – MATCH Maximo}, NOT(@cell = “Does not match”))

This formula counts the number of cells that meet three conditions:

The value of the S/N match field in 2024 is "No match".
The value of the REV_ID match field in 2023 is not 'No Match'.
Maximo match field value in 2023 is not 'No Match'.

The first COUNTIFS function counts the number of cells whose S/N match field value is "No Match" in 2024. The second COUNTIFS function counts the number of cells in 2 whose REV_ID match field value is not "No Match." The third COUNTIFS function counts the number of cells in 2023 where the value of the Maximo match field is not "No Match".

By summing the values of these three COUNTIFS functions, you can calculate the number of cells that match the criteria in one range but not the criteria in the other two ranges.

Summary

To use Smartsheet's COUNTIFS function to count the number of cells that match the criteria in one range, but not the criteria in the other two ranges, follow these steps:

The first COUNTIFS function is used to count the number of cells in a range that match the criteria.
Use the second COUNTIFS function to count the number of cells in the second range that match the criteria.
Use the second COUNTIFS function to count the number of cells in the second range that match the criteria.
Sum the values of these three COUNTIFS functions.

This method is useful when you need to count the number of cells in multiple ranges that meet a condition.

exchangetranslation of

https://community.smartsheet.com/discussion/comment/406547#Comment_406547

Formula to COUNTIFS condition in one range matches but not in other two ranges

Constance Fetter ✭✭✭

11/26/23

Hello:
I'm trying to write a formula that counts the number of cells in a range that return "No Match", but only if no cells in the other two ranges contain "No Match".In this case, we want to count the number of unmatched assets by S/N only, excluding if the other two ranges contain "no match."I tried using the following variation but without success:

=AND(COUNTIF({2023 assets – MATCH REV_ID} = “No match”), NOT({2023 assets – MATCH Maximo} = “No chance”))

or sending us a message on

=COUNTIFS({2023 Assets – MATCH REV_ID} = “No Match”), NOT({2023 Assets – MATCH Maximo} = “No Chance”)))

Like every other formula I've tried, I get an error.Is there anyone who can help?

best answer

jmyzk_cloudsmart_jp ✭✭✭✭✭✭

11/27/23

Hello@Constance Fetter
Try something like: 😀
=COUNTIFS({2023 Assets – Match 2024 MATCH SN}, “No Match”, {2023 Assets – MATCH REV_ID}, NOT(@cell = “No Match”), {2023 Assets – MATCH Maximo}, NOT(@cell = “Does not match”))
The demo below uses the sheet summary field, so the range references are different, but the formula structure is the same.