Report - sum a column

Hi all, I have multiple budget spreadsheets and a related report which I point to one budget sheet at a time. I can then report on just one month at a time (example: project A, October totals). Is there any way to sum the cost column for the items returned in the report?

Find more posts tagged with

Finance

Project Management

Comments

Michelle Watkins

Hi StaceyDe,

Smartsheet's reporting feature does not yet have the ability to total within a report at a hiearchy level. But its coming sometime in 2017 (2Q - 4Q)! This is a feature I have been watching for myself and have asked about.

The interim solution is to add that hiearchy / subtotals in your underlying sheet, then setup a sheet to cell link to the key areas of the hiearchy/subtotals from the underlying sheet(s).

https://help.smartsheet.com/articles/861579-cell-linking

Angie Hatfield

I created a "pre-report", which filters several reports into one sheet. I then can do all of the formatting on the pre-report and then created a report from the pre-report.

It can be a lot of work to set up but works well.

mpalmer

I am looking for this feature also, but can't seem to find it. Is it still on the books for Q4 of this year?

Thanks

Matt

Basti

Any news on this feature?

Cheers Bastian

Ryan Jarymy

I was pretty sure reporting monthly or quarterly estimates vs actuals would be a requirement for our adoption of Smartsheet. Unfortunately there is no simple formula to get the effort from each project sheet row for a given period like what the resource view shows. So instead of a simple formula you can create a nested IF(AND formula to determine if your Start Date and Finish Date fall before, inside, or after the specified date range. The objective of the start and end formula here is to figure out the number of work days that fall in the reporting period.

Here is what you can do:

1. Create a row with the date range criteria and three columns that will contain the formulas below it. E.g. MonthStart (06/01/2018), MonthEnd (06/30/2018), MonthEstimate (no value needed). The formulas in step 2 and 3 will refer to these dates.

2. Create MonthStart formula to find 6 possible scenarios from your start/finish dates using the IF/AND statements to figure out how they relate to your reporting period. I.e. are they before/before, before/between, before/after, between/between, between/after, after/after. Of course factoring in if they are equal to the reporting period start/end is required. The output will either be a date in June using my June example or nothing. E.g. May 15 to June 15, MonthStart result would be 06/01/2018.

3. Do the same kind of formula for the MonthEnd column. Using my last example you would output 06/15/2018.

4. Add your formula in MonthEstimate to count work days between the two derived dates. If you have an Allocation column you could multiply that as well. If you wanted the hourly estimate you just multiply by 8 for a standard work day. E.g. using my example 11 work days times 50% times 8 hours = 44 hours.

I can provide the example if anyone is interested. I’m still working out the kinks due to the => and =< requirements, but the concept is working really well.