# Formula assistance

✭✭✭✭✭✭

Hi,

I currently have a sheet set up where I am calculating the total revenue generated for all the tasks listed underneath the main header using the =SUM(CHILDREN()) formula. All the tasks listed underneath the header in the status column is being classified as awarded, cancelled, pending and unsuccessful as shown in the screenshot below. Would it possible to update the total revenue formula such that it only sums the revenue of the tasks underneath if the status is awarded, pending and unsuccessful? So basically it should exclude the total revenue if the status of any task is changed to "awarded". I am happy to add another helper column in the sheet or create a seperate formula sheet that would just calculate this metric for me because eventually I would like this metric to be displayed in a dashboard so it wouldn't matter where the metric is being stored.

Thanks!

Tags:

• ✭✭✭✭✭✭

Hi @sahilhq!

You can achieve this by using a sumif formula while referencing the children rows for the weighted revenue column for your sum, and the status column for your condition criteria.

Hope this helps!

• ✭✭✭✭✭✭

@cmondo Thanks for the speedy response! I this this will work. What if I want to still keep the weighted revenue SUM(CHILDREN()) metric so that I am getting the total weighted revenue including all the possible statuses and also want to add the formula you have suggested somehwere so that I have a metric for Weighted revenue when status is "Awarded" only. Basically I want my dashboard to display both of these key metrics. I can do a simple cell linking with the total weighted revenue as it's already there. Where can I add the formula you suggested so that I can do a metric out of this as well?

• ✭✭✭✭✭✭

@cmondo I really liked the sheet summary solution! Honestly, ever since Smartsheet rolled out this feature, I have been thinking of a use case for sheet summary but was never able to use it. This is amazing! Thanks for your help!

• ✭✭✭✭✭✭

@sahilhq not a problem! I'm a big fan of keeping sheets as "database-like" as possible and using summary fields for KPI's. it usually lends itself to easier reports from Smartsheet or Power Bi and 3rd party automation like Zapier. Cheers!