# SumIF using Contain

Options
✭✭✭✭
edited 02/18/21

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
edited 02/19/21
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• edited 03/19/24
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!