Dashboard - multiple drop down criterias with sumifs

[cw1628]

Hi All,

I'm looking to create a dashboard that allows the user to select multiple drop downs and the sales $ will reflect what the user chooses.


The main drop down will include:
District:
Store #: Store # will reflect which district it's in
Fiscal week:
Fiscal month:

Each of the drop down will also have an All selection which essentially sums everything.


The 2nd tab will have a data dump that I will maintain as each week data is available.

Would be great if someone can guide me on the best approach as the data dump will exceed 100k row items.

[Glenn Kennedy]

Why not use a Pivot Table?

However, see if this begins to go where you want to go. It's been done for District & Store only. The District is in column C; the District & store combination is in column D (to show you how the formula changes, as elements are added).

There are a number of Named ranges (CTRL-F3 to view edit) which could be made smarter. I have some doubts that this will work for 100K rows... hence my Q about Pivot Tables.

the formulae in dark green are array formulae. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

See what you think and we can work towards getting you able to continue this yourself.

[Glenn Kennedy]

oops . attachment attached.