I have a dashboard where I can select departments via Option Button controls to see their sales. For example:

All Departments
Department A
Department B
Department C
Department D
Department E

I have several other option buttons outside of the department group that can be selected as well. Example:

All States
Michigan
Hawaii
Ohio

All of the sales Data is stored in a separate tab in the format below:

States Department Sales
Michigan Department A 100
Michigan Department B 200
Michigan Department C 250
Michigan Department D 354
Michigan Department E 659
Ohio Department A 205
Ohio Department B 364
Ohio Department C 186
Ohio Department D 183
Ohio Department E 865
Hawaii Department A 975
Hawaii Department B 854
Hawaii Department C 845
Hawaii Department D 751
Hawaii Department E 525

Using SUMIFS I can arrive at the correct number for any specific department. The problem is that if someone clicks on "All Departments" or "All States" I want it to return the sum of sales for either a specific department in all states of all departments in all states (depending on the selection).

The SUMIFS function won't be able to use the criteria "All Departments" or "All States" when it looks in the criteria range because those names do not exist in the raw data.

I have played around with the idea of adding a fourth column with an IF statement that says "If All Departments is selected (the option button) then display All Departments in column 4 otherwise display whatever department is listed in the second column. In theory that would work great it I didn't have 50 states and over 6,000 rows of data. Running a formula down that long slows the entire dashboard down dramatically when selections are made.

Is there an excel function that I can nest or include in the SUMIFS formula that will understand a selection of "All Departments" means Departments A through E, instead of having to nest a complex, 50+ scenario possibility IF statement that looks at the possible combinations that "All" can be selected?

This was a brief summary of my dashboard. I obviously have more columns of data and in total 6 different option groups which all contain various options to include "All" for that specific group.

So you can see where this becomes complex.

I hope that I was able to be descriptive enough about what my problem is!