Reporting like pivot or sumif. How to roll up values by name or category?

jking

Is there a way to combine cell entries and give the total assigned to that category? Ex. 

 

Project Lead      Cost savings

John                      $100

Mary                      $500

Bob                         $400

John                       $200

Mary                        $75

Bob                           $60

 

REPORT = 

John Total = $300

Mary Total = $575

Bob Total = $460

 

p_bureau

Hi,

 

there is not native functionnality that will give you the unique list of a specified column.

 

You can contact the sales team to get access to the Pivot App, but it's pretty expensive!

What I do is I create this unique list myself (so not dynamic at all) and the create sumifs formulas...

 

Not ideal but better than nothing for now!

 

Please let me know if you have other ideas.

 

Best Regards,

 

Paul B

Chris McKay

Hi jking,

There is definitely a way to achieve this, but it requires a little work and is not perfect. But it's much cheaper than the Pivot App as Paul alluded to.

Besides the 2 columns above, you'll need 3 extra Text/Numbers columns (described below).

It's important to note that the formula in the Name ID column in the first row will be different to the remainder, so please remember to populate accordingly or this won't work:

1. Name ID 
   
   First row =IF(LEN(Name1) > 0, 1, "")
   
   Remaining rows =IF(COUNTIF(Name$1:Name2, Name2) > 1, "", MAX([Name ID]$1:[Name ID]1) + 1)
2. Unique 
   
   All rows =IF([Name ID]1 > 0, Name1, "")
3. Totals 
   
   All rows =IF(LEN(Unique1) > 0, SUMIF(Name:Name, Unique1, Value:Value), "")

The first row in Name ID column checks to make sure there is a name in the first column and if so, populates the cell with a 1, otherwise it leaves the cell blank. The remaining rows will check for a duplicate name from the first row to the current row (hence the $) and if a duplicate exists, it will leave the Name ID cell blank, as a unique Name ID has already been assigned previously.

The Unique column looks at the Name ID column and if it contains a value (in this case 1), it pulls the value from Name column (your Project Lead column), otherwise it leaves the cell blank.

The Totals column checks to see if Unique has a value (i.e. it's the first instance of that name) and then uses a SUMIF to total all rows in the Value column (your Cost Savings column).

You can then build a report that only pulls out records with a Name ID value and includes any additional columns you'd like to present.

I hope that all makes sense, but if it doesn't, I've published my example here so you can see the formulas in action: 



https://app.smartsheet.com/b/publish?EQBCT=bd80585462f044c4a2ea62404a6883f3

p_bureau

Chris,

 

thank you for the description of your trick !

 

This will indeed works, but the main issue I often face is hierarchies. The true power of "pivot tables" is the possibility to summarize in different levels...

 

Best regards,

 

Paul.

Chris McKay

Hi Paul,

You're welcome. At a pinch, the solution I posted will work if requirements are simple. Speaking as someone who uses the Smartsheet Pivot App regularly, I agree with you about hierarchies.

That being said, I am also of the opinion that the Pivot App is way too expensive for functionality that already exists in Excel 365 for <$10 per month. In fact, because of Excel's better implementation of filters, it's actually more powerful than the Samrtsheet app. I struggle to understand why I can't filter the results of a pivot from within the Pivot App (e.g. remove blank rows).

If you wanted to look at data hierarchically, however, you could add in another column that identifies the row type (e.g. Region, Resource, Project etc.) based on Parent, Child and Ancestor counts and then adapt my formulas and add conditional formatting based on these identifiers.

Mailys

Hi Chris,

I can't seem to access your sheet : https://app.smartsheet.com/b/publish?EQBCT=bd80585462f044c4a2ea62404a6883f3

Do you mind sharing it again?

Thanks,

Mailys