Drop Menu with static options to count defined cells

[rbone]

Hello,
My title isn't the best description, but this is what I'm look for and a link of the data.

I am looking to have a Macro create a drop down menu (or some other way to make the selection) on worksheet "Agency" with 7 unchanging options. Location 1, location 2, location 3, location 4, etc... (The locations are not named in the workbook anywhere)

Upon selecting "Location 1" search Column A for Agency 02 & Agency 11 then add the numbers in corresponding rows at column G.

Upon selecting "Location 2" search Column A for Agency 07 & Agency 09 & Agency13 then add the numbers in corresponding rows at column G.

Same with Locations 3 through 7, but with different Column A Agencies. The Agency names are not actually Agency **, they are all named differently.

If Agency ** is not found, then count 0 and move to the next.

Place the sum on worksheet "Totals" in cell "B5"

Here is a link to the image if it's not showing: https://drive.google.com/open?id=1T5Tgvpiw4m8x8NlhjFE9d7-h1maeE4A1

 

[SupremeDr]

Can you put this into a pivot table and use a slicer?

 

[rbone]

Can you put this into a pivot table and use a slicer?

As long as the data looks the same and has not been changed on sheet "Agency" when the macro is finished, it can be done in any way that works. Even if it copies the data to a new worksheet to be manipulated, then deletes that worksheet after the total is added the "Totals" worksheet.