Multiple Sheets - SUM Dollar Totals in Report

Tim Meeks

I did a quick search in the community and didn't see this answered exactly, but i think either an Excel export or a future release of Dashboards may provide a quicker/better solution. 

 

So here's what I'm trying to solve. I've got 10 sheets that track Paid Claims.  When the Claim is paid, a field titled "Date Payment Received is populated. I want to be able to get a TOTAL dollar amount of UNPAID claims from all 10 sheets. 

Each sheet has a column that contains a dollar value titled "Claim Amount".

 

I've got a report titled UNPAID CLAIMS that filters by a BLANK "Date Payment Received" field and pulls that row into a report from ALL 10 sheets showing me ALL Unpaid Claims across all 10 sheets.

 

FYI.. I do not total the UNPAID amount in each of the sheets. I would have to have a formula that looks for a blank Date Payment received and sums the dollar amount. And I'm hesitant about putting TOO many formulas in SS because their are usually multiple people updating and thus keeping it updated.

 

To solve the problem quickly today, I just exported the Report into Excel and did a pivot table to get the total dollar by sheet and a total claim amount.

 

Ideas on doing this within Smartsheet or is Excel or the future Dashboard feature going to be the best way to solve?

 

thanks,

Tim

Tomasz Giba

1. You need to have additional column that will have a formula:

=IF(ISBLANK([Date Payment Received]2), 0, 1)

You can keep this column hidden.

 

2. You need to create one header row that contains formula:

=SUMIF([hide this column (1 or 0)]:[hide this column (1 or 0)], 0, [Claim Amount]:[Claim Amount])

Remeber, that this formula can't be in a "Claim Amount" column cos it will be a circular formula.

 

3. Make this in all 10 sheets. So every sheet will have a sum of it on the top of it.

 

4. Link all these 10 sheets (specific cells) to another summary sheet and sum them there.

APJ Enablement

Short Version: I would add the formula & hide those columns on the sheet.

 

Long Version:

I designed a Training Management solution for one of my customers. The "Task Template" for each training session has 136 columns (most of them formulas) and 155 Conditional Formatting rules. 

 

The solution has 200+ sheets and ~50 reports to date and we haven't seen any performance issues.

APJ Enablement

PS - And I agree with Tomasz.

1. Add & Hide the formula columns.

2. Create a new Master Summary sheet.

 - Row 1 = Parent w/ formula: =SUM(CHILDREN())

 - Row 2 = Child w/ "Cell Linking". *I prefer to copy & 'Paste Special' for cell linking*

 

Tim Meeks

Tomasz & Kris,

 

thanks for the quick replies and solultions. I had a few minutes and got the first formula working. I hope to have some time tomorrow to work on it some more.

 

I'll let you know if further questions and how it works out!

 

THANKS!

 

Tim