Summing Data Based on multiple criteria that changes

Hi Everyone,

I am looking to get some input on if there is an Excel function that will help me achieve my end goal. I do not have a spreadsheet to share as I have not yet begun building my file.

I have detailed department budget data which is broken down by accounts within all of my departments. I have data in all 12 months (there is a separate column for January - December with different amounts in each month).
Column A of my data is the department number, column B is the account number and columns C through N have values for January through December.

I want to create a consolidated summary tab that sums up each account for a couple departments at once for a specific month.

To illustrate, I have department numbers 10,11,12,13,14,15. Within each department is account 50,100,200,300.

I want to show the sum of each account for department 10 and 11. My summary tab will have the account numbers 50,100,200,300 in cells C4:C7. Cell D4 will have the June total in account 50 for departments 10 and 11 only. I will have a drop down menu where you can select the department numbers to include in the summary. Therefore, I would be able to change my criteria from department 10 and 11 to department 12 and 13 and have cell D4 recalculate the June total in account 50 for departments 12 and 13 this time.

Does this make sense. Does anyone know if building something like this is possible in Excel without using macros?

All thoughts are appreciated.

I was thinking SUMIFS as well, but I wasn't sure how the drop down menu would be built into the function. The intention is to either select multiple departments in the drop down menu, or create department combinations on a separate tab and give each combination a named range. When I click the drop down menu and select "Marketing" for example, it would automatically know to include department 10 and 11 in my sum.

Do you have any ideas?

I am attaching a workbook I created to better illustrate by question.

Thank you for solving this for me. This formula should work perfectly, but when I apply it to my much larger data set, I get the dreaded:

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, there formulas cannot be evaluated."

Any ideas how to break up this formula so the worksheet can calculate properly?

This worked like a charm! I was able to adapt the formula to my much larger set of data.

Thank you!!

I would like to add one more level of complexity to my question.

Some of my items in my drop down list are multiple words. Is there a way I can keep them as two words in the drop down list but still reference the table in the named range?

If you look at the solution file provided by Jacc above, can I remove the "_" from "World_Wide" in my drop down list and replace with a " " and still have the array formula in Cell D4 work correctly?

I know this was marked as solved, so I can open a discussion if that is preferred.

Thank you.