=SUMIFS with Criterion3

=Chris Palmer

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.

Ryan Sides

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

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")

=Chris Palmer

https://community.smartsheet.com/discussion/comment/354400#Comment_354400

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

Ryan Sides

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

=Chris Palmer

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

=Chris Palmer

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.

=Chris Palmer

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

Ryan Sides

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