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

Courtney George

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!

-Courtney

MCorbin

Yes, you can do that.

Your "Declined" formula would be:

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

Pending:

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

Accepted:

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

Completed:

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

These will update as your status and assistance $ change.

Courtney George

Thank you so much!!!

Courtney George

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

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.