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
Check out the Formula Handbook template!