Sign in to join the conversation:
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
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
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:
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
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.
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.
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
Hello , I am looking for help to write a formula that will check a box if certain criteria are met. I have students that are signing up for a class and they need the previous class or classes as prerequisites. for example i have class 1, class 2 , class 3 and class 4. each higher number class needs to have completed the…
Hello, I am looking for formula help where I want to return the earliest date in a range for different workstreams groups on a project. The source sheet is formatted as a date field, and the formula used below is returning a 0 no matter what I do. Any suggestions? =MIN(COLLECT({Project Plan - start date}, {Project Plan…
I currently have 14 sheets with the following columns: Batch # and Reviewer I use an Index Distinct formula to acquire the unique batch numbers from all 14 sheets and put them into 14 columns on the 'metrics' sheet. I then use another index distinct to get a list of all the unique batch numbers into one 'Unique Batch…
