Countifs with multiple sheet references and CONTAINS

MoyaC

Hi!

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!!

Leibel S

@MoyaC

The below should work for you:

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

Leibel S

@MoyaC

The below should work for you:

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

MoyaC

@Leibel Shuchat Thank you so much! That worked perfectly!

So appreciate the prompt response!😀