+ Reply to Thread
Results 1 to 3 of 3

Advanced SumIfs for dashboard building (slicer replication)

  1. #1
    Registered User
    Join Date
    03-19-2020
    Location
    CHile
    MS-Off Ver
    2013
    Posts
    2

    Advanced SumIfs for dashboard building (slicer replication)

    Hey guys!

    I hope you're doing well during the outbreak and staying safe.
    I've got an interesting one and I'm desperately looking for help. In essence, I have to replicate what a pivot + slicers do with formulas and drop down lists. Before going any further, if there is a way to turn slicers into drop downs, problem solved (my boss insists on not using slicers.

    This project works with volumes. The idea is that you sum all volumes from a table based on the criteria the user selects to obtain a new filtered target volume (i.e. something easy to do with pivot tables and slicers).

    So far, I've managed to correctly get information for any combination of "Occations" + any combination of criteria such as "Age", "Gender", "Economic level" and "Channel" as long as you use only 1 level. The problem is, the user could potentially select more than 1 level per criteria + any combination of Occasions e.g. 3 age group + 2 economic levels + 1 channel for a combination of 6 different occasions to narrow the volume pool.

    Snip 1.PNG
    The target cell is in green, and manages to take into account all cells in red. The idea is to have the same result as the pivot with slicers.

    Hope you can help!
    Best
    Carlos
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,875

    Re: Advanced SumIfs for dashboard building (slicer replication)

    Carlos,

    Given this is a one off type calculation you might consider switching to a SUMPRODCT based approach, e.g.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above should replicate your slicer driven results
    (if needed modify , delimiter to ; per your locale)

  3. #3
    Registered User
    Join Date
    03-19-2020
    Location
    CHile
    MS-Off Ver
    2013
    Posts
    2

    Re: Advanced SumIfs for dashboard building (slicer replication)

    Thanks a lot XLent!
    Just tried it out and it works perfectly. I really liked how you matched the criteria range to the range of options; matching range v range w + counta function is genius and new to me, I'll be remembering the trick for sure.

    Have a great weekend and stay safe
    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 11-07-2019, 04:13 PM
  2. advise on building a dashboard with my data
    By dougers1 in forum Excel General
    Replies: 6
    Last Post: 12-09-2018, 06:53 PM
  3. Slicer Hyperlink, Investment watchlist dashboard
    By tbazzard in forum Excel General
    Replies: 0
    Last Post: 11-22-2018, 07:54 AM
  4. Trouble building a dynamic Dashboard
    By Joao Aleluia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2017, 07:37 PM
  5. [SOLVED] Advice on building a dashboard
    By ClareLou in forum Excel General
    Replies: 1
    Last Post: 02-08-2016, 11:45 AM
  6. Freelancers for Dashboard building
    By Katherine82 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-13-2015, 02:13 PM
  7. help building a dashboard or graph !
    By masond3 in forum Excel General
    Replies: 1
    Last Post: 01-12-2012, 11:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1