Formula assistance

sahilhq

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.

Capture.PNG

Thanks!

Chris Mondeau

You could shift the awarded amount above the status column like the picture below, then add a sum(children) to the original cell.

Depending on how the rest of your sheet is set up, you could also use the sheet summary fields to list out other key metrics as well.

A thing to note about this is it will sum the parent level rows, so you might need to change the structure of your sheet, or another column to use a filter in part of the sumifs function.

Chris Mondeau

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!

sahilhq

@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?

Chris Mondeau

You could shift the awarded amount above the status column like the picture below, then add a sum(children) to the original cell.

Depending on how the rest of your sheet is set up, you could also use the sheet summary fields to list out other key metrics as well.

A thing to note about this is it will sum the parent level rows, so you might need to change the structure of your sheet, or another column to use a filter in part of the sumifs function.

sahilhq

@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!

Chris Mondeau

@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!