SumIF using Contain

Naina Dave
Naina Dave ✭✭✭✭
edited 02/18/21 in Formulas and Functions

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Naina Dave
    Naina Dave ✭✭✭✭
    edited 02/19/21

    @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
    Mark Cronk ✭✭✭✭✭✭
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Naina Dave
    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
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!