Sum a column of numbers only if they meet certain criteria.

I hope this makes sense.

I am trying to add up all the numbers is column "Slides". But I only want to include the numbers if the done is not checked. I also not not want to include the number if column "Status" says Scoring Complete. Column "status" has many different options in it. I only need to total to be in one cell, but this sheet is forever changing so I want that cell to update on its own. Thanks for the help in advance!

Best Answer

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

    Hi @Sarah Moore ,

    I'm guessing at your column names so you'll need to make some adjustments, but try:

    =SUMIFS([slides]:[slides], [done]:[done], 0, [status]:[status], <>"scoring complete")

    Work?

    Mark


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

Answers

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

    Hi @Sarah Moore ,

    I'm guessing at your column names so you'll need to make some adjustments, but try:

    =SUMIFS([slides]:[slides], [done]:[done], 0, [status]:[status], <>"scoring complete")

    Work?

    Mark


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

  • Yes! Thank you so much! it worked

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Excellent. Please accept my answer to close the discussion. Thanks for contributing to the Community.

    Mark


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

  • I have one other question. So i used the same formula for calculating something else.

    =SUMIFS([Number of Slides]:[Number of Slides], Done:Done, 0, Status:Status, "Stained")

    And it works but i want it to add the "stained" and "staining in progress" in the same column. how to i rewrite the formula in order for it to do this? "Staining in progress" is also listed as an option in the same status column.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Sara,

    =SUMIFS([Number of Slides]:[Number of Slides], Done:Done, 0, Status:Status, OR("Stained", "Staining in Progress"))

    Work for you?

    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!