Dynamic Unique values for Dashboard
Goal: I want to dynamically extract "Top 5 Customers by Spend".
Context:
-Sales Dept. uses a sheet to register future projects. Important columns: "Customer Name" and "Spend".
Considerations:
- "Customer Name" could appear more than 1.
-In rare occasions, two "Customer" can have the same spend, so both should be considered in "Top 5".
So far:
I tried having a helper sheet with:
a) Having a HELPER COLUMN to count COLUMN NAMES
b) Having a 2HELPER COLUMN to SUMIF to add SPEND by COLUMN NAMES
…but I cannot do it both DYNAMICALLY and COLUMN FORMULA.
I almost lost all hope. I just want to double check if someone found another solution.
Answers
-
There are a number of solutions for this. Are you able to provide some screenshots for context?
-
This is a dummy sample, please note:
- SupplierD & H have the same value
- Suppliers can be repeated
I have not figure out an option that allows:
- Update range automatically
- Sort Top 5 dynamically -
I can't see the column name that contains the numbers 1 - 5, so in this example I will just call it "Rank".
In the [Column6], you would use this formula:
=LARGE(Spend:Spend, Rank@row)
Then in [Column5] you would use:
=INDEX(COLLECT([Supplier Name]:[Supplier Name], Spend:Spend, @cell = [Column6]@row), COUNTIFS(Spend:Spend, @cell = [Column6]@row, Rank:Rank, @cell <= Rank@row))
-
This is not the correct approach for the following:
a) SUMIFS: The goal is to dynamically extract TOP SUPPLIERS BY SPEND (overall). Then, it must be SUMIFS instead of LARGE.
b) Unique values: I tried various approaches but nothing that can handle NEW VALUES AUTOMATICALLY (cannot make them column formulas) or figure out a helper solution with no manual interaction.
In Excel terms it is something like "MATCH + INDEX with multiple results" or "Unique values + Xlookup" or even the result obtained with "Pivot Table > Organize top first". Anyway, thanks for trying.
Note: I manage various workspaces for different departments. No backup. I was trying to have simple dashboards per team. Showing "Top X" updated 24/7 is a challenge if you need to manually drag a formula, or manually apply filters. -
Did you try my solution?
-
Yes, is not the correct approach.
Thank you for the effort. Maybe I didn't explain it correctly.
SUM not LARGE
This screenshot shows the desired result. For 'SupplierA' shows the TOTAL SUM (5+8+2) = 15.
The solution proposed uses LARGE, not SUMIF/SUMIFs. Hence the second part of the solution doesn't work.
a) Applying LARGE instead of SUMIFIt leads to errors.
The objective:
- Extract top values (as summary)
- Extract names (related to previous step)
- Dynamically.
Personally, I almost achieve the desired goal using various column helpers but can't solve:=COUNTIF([Customer Name]$1:[Customer Name]@row, [Customer Name]@row)
I cannot think of a workaround for this formula that is suitable for 'Convert to Column Formula'.**I use this helper step to extract UNIQUE NAMES … > then COLLECT values > then Top VALUES > then Reorder Highest first > then MATCH values with UNIQUE NAMES.
I guess I'll continue to drag formulas, or using Excel.
Thanks for trying. -
Getting the COUNTIF for row number as a column formula requires an auto-number column. Then the column formula for outputting the row numbers is
=MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!