Hi

I'm not really sure if you want to do these actions on each and every workbook, or if you want to consolidate onto a separate workbook. But here's some starting ideas.

1) The auto filter will give you the drop downs for your raw data. You can then do your filtering by location.
2) You could use the advanced filter to determine a unique list of locations and status. You can then use the SUMPRODUCT function to obtain a count of the combinations.

If you attach an example file showing your structure and some represenatative data, then it would be easier to provide some solution ideas on that example data.


HTH

rylo