Want sum calculation in report - Formulas in reports?

Stan Ward

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

 

JamesR

If you use a Project sheet and the Assigned too the Resource View may do what you want to a certain degree.  Bear in mind that it works in days not Hours etc at the moment.

Durations are due to change in a release shortly.

 

Reports do not allow calculations to be aadded

 

It may be fidley but you may be able to do something on the lines of a Sumif function.

Stan Ward

Hi JamesR - I have the "Advanced" subscription, not Team or Enterprise, so I do not have the Resource View. Maybe someday when I grow my company. In the meantime, this is mostly for me. What do you mean by "It may be fidley but you may be able to do something on the lines of a Sumif function"? I assume you mean in a sheet not a report. I really need it in the report. Thanks, Stan

Travis

Stanward - you could build your report, export it to Excel, then import it back into Smartsheet where you can add formulas to calculate hours worked. This would require a little more work for you (exporting and importing when changes are made) but it could be a good workaround until you are able to purchase a Team or Enterprise plan which does support resource management. 

Stan Ward

Thanks Travis.  If I could export to Excel to do the calculation, is there a way to instead have a sheet take values from a report and do the calculation there, thereby keeping it all in smartsheet?  Alternatively, while I have never done this, is there way to have a live/real-time link between a smartsheet report and a Google Sheet, thereby doing the calculation that way?

 

Thanks,

Stan

Travis

 

You won’t be able to create formulas in a report but you can import the Excel file back into Smartsheet, where you can create formulas. There isn’t a way to create a live link to Google Sheets but you could always export your report to Google Sheets where you can create your formulas (or import it back into Smartsheet where you can do the same).

Stan Ward

Thanks Travis.

Daniel Hauber

This question and the responses are very useful, thank you all.

 

I think my issue is very similar. I'm using my report to pull rows from multiple sheets, and I want to SUM or otherwise total the quantiative columns (employee hours) by sheet. I.e., I want subtotals by the several different sheets, within the report. 

 

Is this possible, or is using formulas in reports a possible future feature?

 

Thanks!

Travis

Hi Daniel, it is not possible to build formulas in a report (apart from the automatic formulas that appear at the bottom right of a report when you highlight cells). We are tracking feedback for this request and I will add your vote for it! 

Christopher Maselli

I'm on the trial right now and LOVING Smartsheet, but this is exactly what I'm trying to do now. I was able to run a report to pull out total columns from another sheet, but I want to add a formula under those rows to show totals and maybe averages. This could be REALLY useful.

JamesR

Yes it would but unfortunatly you are currently un able to  do that.  Remember that the "Report" is not realy a report, its a live view of data filtered according to your requirements.

You may be better creating a new sheet with linked cells to the total clumns in the other sheet and the summing them in that.  Obviously also link in other columns to give you the details.

Leo Plant

+1 vote for formulas in reports!

Christian Wells

Travis Please count my vote for Formula's in Reports

At least to have the aggregate functions of;

Count, Sum, CountIf, SumIf, CountIfs, SumIFs, Avg in to a report.....

 

P.S. Also into Dashboards (Beta)

Travis

I have added all your votes!

 

Christian, did you know there are some formulas that you can use for quick calculations in your report? To use them, highlight a group of cells and depending on the data type, you will get calculations on the bottom right of the report. For example if you select a group of numbers, you get a SUM, COUNT, and AVG.

Christian Wells

Yes travis, I was aware of that, But i am also playing around with Dashboards (Beta) and looking for a way to display a summary of a pure list of data in a smartsheet, without having to have a total summary row on the data sheet....

 

It would be great to breakdown a list to grouping by status/person, etc. outside of parent/child, on a summary report (not a detailed report).

 

Obviuously having these same calculations as a widget on a Dashboard would also be of help.  I note this is already on the Dashboard feedback.

 

Thanks travis, your always on top of everything, Im sure the whole community thanks you.

JamesR

Christian,

I do not know what Licence you have but if you have Enterprise you can make use of the ODBC connector with Excel.  It costs more and may not be justifable, however the Dashboard is also going to cost more as is the Calendar view so it is a case of Cost benifit analysis as to which way to go.

In excel you can then do:

• -Pivot Tables
• -Charts
• -More complex calculations