SumIF using Contain

Naina Dave

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.

Mark Cronk

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

Mark Cronk

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

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

Mark Cronk

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

Naina Dave

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

Mark Cronk

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

Naina Dave

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

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark