=SUMIFS with Criterion3

Mr. Chris
Mr. Chris ✭✭✭✭✭
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.

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!