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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.



  • 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 SUMIF

    It 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!