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.

  • Pamella Souza
    Pamella Souza โœญโœญโœญ
    edited 03/19/24

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!