# Layered functionality

Hello community!

I have a master project sheet that is auto populated from an intake form for entire organization. I am setting up different division portfolio metrics. Each division has multiple "locations" within it. I am currently looking to modify the Portfolio metrics sheet for "projects by status." My issue is that I am referencing the master sheet so I need some filtering that only affects the division I am working on. I was able to create a formula to list just the number of projects that met the "location" requirement for that division. This essentially acts as the filter.

=COUNTIF({Location}, ="Dept. 9-10") + COUNTIF({Location}, ="Dept. 9-11") + COUNTIF({Location}, ="Dept. 9-12") + COUNTIF({Location}, ="Dept. 2-21") + COUNTIF({Location}, ="Dept. 2-23") + COUNTIF({Location}, ="Dept. 2-24") + COUNTIF({Location}, ="CLE-Electronics Factory") + COUNTIF({Location}, ="Machine Division")

Now I would like to count the number of projects by status within those locations. I am using the below formula but it is not taking into account the "filter" for location before collecting the status from the label row.

=SUMIF({Location}, ="Dept. 9-10") + SUMIF({Location}, ="Dept. 9-11") + SUMIF({Location}, ="Dept. 9-12") + SUMIF({Location}, ="Dept. 2-21") + SUMIF({Location}, ="Dept. 2-23") + SUMIF({Location}, ="Dept. 2-24") + SUMIF({Location}, ="CLE-Electronics Factory") + SUMIF({Location}, ="Machine Division") + COUNT(COLLECT({Location}, {Activity Status}, \$Label@row))

There may be a much simpler way to accomplish this task, but any help would be greatly appreciated!!