=SUMIFS with Criterion3
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
-
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")
Answers
-
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, 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.
-
@Mr. Chris no worries. What is your updated formula?
-
=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.
-
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.
-
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!!!
-
@Mr. Chris all good! Glad you got it working.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!