Want sum calculation in report - Formulas in reports?

Does anyone know if there is a way to add a formula for a report?

What I want to do is sum the values of a column for all rows returned for a report and to add some sort of highlight when the sum exceed a certain value. Specifically, if the amount of hours for a list of tasks for day assigned to a given person exceeds eight hours, I want the report to highlight this fact. The report is only for one person. Doing it in a sheet (as opposed to a report), is not an option, as the report is pulling tasks (rows) from multiple sheets. The task on each sheet has a value for the remaining time to complete that task. I do not want people having too much scheduled for a given day and want their "daily to-do list" to be highlight for them when they have too many things on the list. "Highlighting" them can be as simple as a single cell or column being highlighted/colored.

Thanks,

Stan

Find more posts tagged with

Reports

Product Development

Conditional Formatting

Project Management

Enhancement Request

Comments

Dave B

Let say you have five sheets and you want a single report that pulls info from all five, and then tallys some of the info on the report.

Can't do that can we? Okay, so create a sixth sheet using cell linking that pulls in the info you need, then write the formulas to do the math you want. Then create the report from the sixth sheet.

Brad Jones

+1 vote for formulas in reports.

I don't think it should be necessary to creat extra calculation sheets, or add extra calculation rows (which you can't hide) to your data sheets and then link them to the report. This is unnecessary extra data/work.

Dave B

My two cents (FWIW): In a wider sense this discussion revolves around the defintition of what a report is. To me a report is a static presentation of previously calculated data. To add formulas to a report is change the definition of a report. Thus making it nothing more than a sheet.

Brad Jones

That's a fair point Dave. In fact, now I'm thinking I may just create a sheet with view-only rights for what I'm doing.

Dave B

Brad,

What I've done is this . . .

Let's say my sheet has data in Columns 1 - 5.

I want to calculate (and then report) the totals in Columns 1 and 3, so I have a formula at the bottom of each column.

Now, I create Columns 6 and 7. Then I use Column 6 as a label for Column 7, which is populated using a formula with the results of my formula in Columns 3.

I do the same in Columns 8 and 9 regarding the results for Column 5.

Then I build a report showing only Columns 6 - 9.

Using row-based parent/child relationships you can get really creative in your reports. For example, let's say row 1 is parent and 2 - 5 are children, row 6 is a parent with rows 7 - 10 as children, and so on. If Column 1 is the defining label (say, Client Name), then copy that over to the same row in your "report" columns. Then copy down the same in each child row in the same column. Then you can build a report for EACH client from this single sheet by setting the report criteria to filter on their name for each row in their report column.

JamesR

Although Formulae in Reports may be useful, we need to think also of what infact a report is. In SQL database terms its a "View". It is a viertual table, it does not physicly exist in a sheet form like a sheet. The problem is that people think of the flexability of Excel and want the same in Smartsheet. Smartsheet is not a Spreadsheet, it is an enhanced set of database tables with added functionality.

With a little lateral thinking and most important planning you can usually achieve the results/outputs you want.

Plan, then replan and then plan again. Evolution is only half the solution and inevitably takes longer

Mattisphere

James,

If it were really replicating SQL features, it would be easy to do calculations and pivot data. That functionality is not available in Smartsheet like it would be through a database. I think they are working towards that level of flexibility, but Excel evolved over decades with huge programming teams. I don't expect that same level of functionality overnight, but appreciate every month when they release new formulas/enhancements. The dev team continues to do great things.

Angie Hatfield

Please count my vote to add sum and count formulas to reporting.

I have two locations using sheets that I am trying to report on but now may have to create a linked sheet, which is better than nothing, but creates more work since I may have to constantly link in new data.

Thank you

Angie Hatfield

Here is what I did. I created a template that I then used to create sheets for each location.

I then created another sheet and linked the cells that had the data I wanted to report on.

I added another column with a sum formula of those columns that are reporting data.

I then created a report that only pulled data from this last sheet that has the formulas.

It took a little bit of time to set up, but looks like it will work beautifully.

André Gratton

Please count my vote to add formulas to reporting.

Jason Guinn

Please add me to the list of individuals voting for this functionality. I am using Smartsheet for budgeting and would find it helpful to be able to pivot data in reports. For example, a view to show revenue subtotaled by department and location would be fantastic.

Srini Chetlur

Same here - I'm voting for the formulae in the reports.

Tom Theado

This post is older, but I was wondering if there is now a way to see how resources are allocated for a specific day/week etc. across multiple projects (sheets)?

Thank you!

Mattisphere

Stan, I'd suggest using PowerBI - you can add calculated columns or turn totals rows on and use conditional formatting. It is a powerful reporting tool and you can use Smartsheet to store your data and use its collaboration features. I plan on using reports as simple filtered editing tools so I can show only the data I'm interested in editing at any given time. For reports that need to communicate something to others, Power BI is a good tool - you can make dashboards in it as well.

Anna B

+1 to the request for adding basic calcs like sum/count to reports!

Thanks,

Anna