# Countifs with multiple sheet references and CONTAINS

Options
✭✭
edited 09/02/22

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

• ✭✭✭✭✭✭
Options

The below should work for you:

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

• ✭✭✭✭✭✭
Options

The below should work for you:

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

• ✭✭
Options

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

So appreciate the prompt response!😀

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!