sumifs and contains

Good morning. I am having an issue with a sumifs formula and I could really use some help. I am using 2 sheets. One sheet is where the live data is being stored and the 2nd sheet is where I am pulling data for specific reporting. Here is my formula that I am getting incorrect argument and I cannot figure out why. I am assuming its because I have a drop down list that has multiple selections. I am summing up the total hours for a project during a month but it is for different sites or projects.


=SUMIFS({Total Hours}, {Project}, Project4, (CONTAINS("01/", {Weekdays}))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Rook

    In a SUMIFS, first you'll need to list the {range} and then the Criteria. In your formula you have CONTAINS as the criteria without the range listed first.

    I would actually suggest using HAS instead of CONTAINS. HAS is built specifically for multi-select columns to see if a cell has that one selection along with others.

    Try something like:

    =SUMIFS({Total Hours}, {Project}, Project4, {Weekdays}, HAS(@cell, "01/"))


    Let me know if this works for you!

    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 @Rook

    In a SUMIFS, first you'll need to list the {range} and then the Criteria. In your formula you have CONTAINS as the criteria without the range listed first.

    I would actually suggest using HAS instead of CONTAINS. HAS is built specifically for multi-select columns to see if a cell has that one selection along with others.

    Try something like:

    =SUMIFS({Total Hours}, {Project}, Project4, {Weekdays}, HAS(@cell, "01/"))


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!