Sumifs Formula with a Contain

I am trying to get the sum of hours worked IF the "Office" Column = Administrative Solutions. The Office column is a multi-select dropdown.


=SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, CONTAINS("Administrative Solutions", @cell"))


Any help would be appreciated! Thank you!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Maria Watters

    Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.


    =SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))


    Let me know if this works for you! If not, it would be helpful to know what error or issue you're seeing with the formula, and a screen capture of the sheet you're referencing (but please block out any sensitive data).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Maria Watters

    Since you're looking in a multi-select column, try using the HAS function. It's specifically designed to search for one selection in a multi-select cell.


    =SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, HAS(@cell, "Administrative Solutions"))


    Let me know if this works for you! If not, it would be helpful to know what error or issue you're seeing with the formula, and a screen capture of the sheet you're referencing (but please block out any sensitive data).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Maria Watters
    Maria Watters ✭✭✭✭✭

    Thank you so much! That did work! What is the difference between HAS and CONTAINS. I believe I have used contains before on a multi-select drop down as well.

  • No problem, I'm glad it worked for you!

    CONTAINS will see if a cell contains the text. So, say you were searching for the word "App" but one of the options was also "Apple". It will see the app in Apple and count that as 1.

    HAS looks for an EXACT match, start-to-finish. Let me know if that makes sense or not! I'd be happy to post screen captures to illustrate the difference.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Maria Watters
    Maria Watters ✭✭✭✭✭

    @Genevieve P. Thank you so much! This is VERY Helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!