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

Carlos,

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

Formula:

Please Login or Register  to view this content.

the above should replicate your slicer driven results
(if needed modify , delimiter to ; per your locale)

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