Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    =SUMIFS with Criterion3

    Community Champion
    edited 12/14/22 in Formulas and Functions

    Hello Smartsheet Community!

    For the life of me I can't get the Criterion3 to work in my =SUMIFS formula.

    This formula works beautifully with Criterion1 and Criterion2:

    =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row)

    I want to use the exact same formula in a new column and add Criterion3:

    =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, [QD]:[QD], [Q]:[Q], "Q1 2022")

    However this one doesn't work for me I get #INVALID DATA TYPE

    and it removes my brackets [ ] from [QD]:[QD], [Q]:[Q]

    Thanks so much in advance.. Been staring at this for hours.

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      Hey @Mr. Chris your criteria is missing a match criteria for QD.

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, [QD]:[QD], you need to tell it what to match to here, [Q]:[Q], "Q1 2022")

      If you're looking for QD to be blank, use double quotes ""

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, [QD]:[QD], "", [Q]:[Q], "Q1 2022")

      Ryan Sides

      Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

      Come Say Hello!

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hey @Mr. Chris your criteria is missing a match criteria for QD.

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, [QD]:[QD], you need to tell it what to match to here, [Q]:[Q], "Q1 2022")

      If you're looking for QD to be blank, use double quotes ""

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, [QD]:[QD], "", [Q]:[Q], "Q1 2022")

      Ryan Sides

      Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

      Come Say Hello!

      · Share on FacebookShare on Twitter
    • Community Champion
      Ryan Sides

      @Ryan Sides, thanks for such a speedy reply. I tried this again with your feedback and it doesn't work. I think the formula thinks I'm still looking for criterion2. I want to search for "Q1 2022" in the QD Column, and give me the sum of the Invoice Amount, where it matches the CapEx #. Does this make sense? I get an #INCORRECT ARGUMENT SET now.

      https://www.linkedin.com/in/zchrispalmer/

      · Share on FacebookShare on Twitter
    • Community Champion

      @Mr. Chris no worries. What is your updated formula?

      Ryan Sides

      Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

      Come Say Hello!

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/14/22

      @Ryan S

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, "Q1 2022", Q:Q, QD:QD)

      Smartsheet removes the brackets [ ] after I hit enter.

      https://www.linkedin.com/in/zchrispalmer/

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/14/22

      I also tried this formula and received #INVALID DATA TYPE.

      =SUMIFS([Invoice Amount]:[Invoice Amount], Status:Status, "Approved", [CapEx #]:[CapEx #], [CapEx Number]@row, QD:QD, "", Q:Q, "Q1 2022")

      Again when I hit enter the [ ] are automatically removed.

      https://www.linkedin.com/in/zchrispalmer/

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 12/14/22

      I feel silly.. at the bottom of the QD column there was a # that broke the formula. I removed the character and now the formula works.. Thank you!!!

      https://www.linkedin.com/in/zchrispalmer/

      · Share on FacebookShare on Twitter
    • Community Champion

      @Mr. Chris all good! Glad you got it working.

      Ryan Sides

      Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

      Come Say Hello!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions