SumIF using Contain
Hello,
I am trying to write a formula to sum a column if a criteria in another column is met.
Column to sum : Amount
Criteria column name : Status
Criteria to be met: Completed
I am trying =SUMIF(Status:Status, CONTAINS "Completed", Amount:Amount) but getting #UNPARSEABLE ERROR.
Also, is it possible to include multiple criteria from multiple columns?
Example: Sum of column Amount, if Status column contains "Completed, if Quarter column contains "Q1, if Budget column contains "Fixed". If all these 3 criteria are met, then sum the column Amount.
I am new to Formulas in Smartsheet. Please can someone help.
Best Answers
-
Hi @Naina Dave ,
Try:
=SUMIF(Status:Status, "Completed", Amount:Amount)
To use multiple criteria use SUMIFS. The syntax is: =SUMIFS( range , criterion_range1 ,criterion1, [ criterion_range2, criterion2... ] )
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Try:
=SUMIFS(Amount:Amount, Status:Status, "Completed", Budget:Budget, "Fixed", Quarter:Quarter, "Q1")
The formula will sum amounts if the status is complete, the budget is fixed and the quarter is Q1.
If your column name is a single word you don't need to wrap it in brackets.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Naina Dave ,
Try:
=SUMIF(Status:Status, "Completed", Amount:Amount)
To use multiple criteria use SUMIFS. The syntax is: =SUMIFS( range , criterion_range1 ,criterion1, [ criterion_range2, criterion2... ] )
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk thank you for the quick reply.
The first formula worked successfully !
For the multiple criteria, would that be as below?
=SUMIFS(Status:Status, "Completed", [Amount]:[Amount], (Budget:Budget, "Fixed", [Amount]:[Amount]), (Quarter:Quarter, "Q1", [Amount]:[Amount]))
OR
=SUMIFS([Amount]:[Amount], [Status:Status, "Completed"], [Budget:Budget, "Fixed"], [Quarter:Quarter, "Q1"])
-
Try:
=SUMIFS(Amount:Amount, Status:Status, "Completed", Budget:Budget, "Fixed", Quarter:Quarter, "Q1")
The formula will sum amounts if the status is complete, the budget is fixed and the quarter is Q1.
If your column name is a single word you don't need to wrap it in brackets.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk YES! YES! YES!... It worked... thank you so very much!. two of my criteria columns had special character/space, so I had to use square brackets. Your formula worked... phew.. thank you!
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!