SumIF using Contain

Question

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 · Accepted Answer

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 · Accepted Answer

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 · Accepted Answer

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

Mark