How do I create a SUM based off of specific criteria?

I created a smartsheet to track an assistance program. I created a column with color-coded dots (RYG Balls) to represent the status of each application. These dots are changed manually as the application status changes. Red dot (declined), Yellow (Pending), Green (Accepted), and Blue (Completed). A different column has the amount of assistance we are providing for each application.

I wanted to create a sum to represent all of the assistance (in dollars $) that have been allocated for the applications coded as blue and green - is this possible?

It would be great if this could update on its own as we receive applications and manually choose their status because at this point I have to create a filter and manually change the total each time and it's becoming cumbersome.

Thank you!



  • MCorbin
    MCorbin Overachievers Alumni

    Yes, you can do that.

    Your "Declined" formula would be:

    =SUMIFS(Assistance:Assistance, Status:Status, "Red")


    =SUMIFS(Assistance:Assistance, Status:Status, "Yellow")


    =SUMIFS(Assistance:Assistance, Status:Status, "Green")


    =SUMIFS(Assistance:Assistance, Status:Status, "Blue")

    These will update as your status and assistance $ change.

  • So I just tried this and it didn't work - When I entered the formula it says "#UNPARSEABLE".

    I am a newbie to smartsheets and creating formulas. The column that includes the RYG Balls is titled "Status". The Smartsheet is titled "Rental Assistance".

    Could you give me a quick explanation of how you created this formula? This might help me troubleshoot the issue.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Courtney George

    If you want to Sum when it's either blue or green try this:

    =SUMIFS(Assistance:Assistance, Status:Status, OR(@cell="Blue",@cell="Green"))

    Watchout for ";" and "," and check out that values like blue or green are spelled like it is supposed to be (you can check on clicking on any status cell. As far as I know, this is case sensitive.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!