Countifs with multiple sheet references and CONTAINS

MoyaC ✭✭
edited 09/02/22 in Formulas and Functions


I am struggling with how to build the correct formula to provide the result of the following scenario:

I am using the Smartsheet "Fruit Sheet" to create a formula in the Smartsheet "Orchard"

Search "Fruit Sheet" for any "Apples" that may be "Red" in the "Summer"

The sheet has several fruits and several apples. The column for red is a drop down that can also include green, pink,...

The formula I have only finds if there are apples in the summer and are only red (cannot be any other color). However since many apples can be selected to be more than one color I am not getting the correct answer. I think I need a CONTAINS function in the formula but I can't get it to work right.

Current formula in "Orchard"

=COUNTIFS({FRUIT_SHEET_FruitType}, "Apple", {FRUIT_SHEET_Color}, "Red", {FRUIT_SHEET_Season}, "Summer")

Tried but not correct:

=COUNTIFS({FRUIT_SHEET_FruitType}, "Apple", {FRUIT_SHEET_Color}, CONTAINS"Red", {FRUIT_SHEET_Season}, "Summer")

=COUNTIFS({FRUIT_SHEET_FruitType}, "Apple", CONTAINS{FRUIT_SHEET_Color}, "Red", {FRUIT_SHEET_Season}, "Summer")

Thank you!!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!