Layered functionality

RSmith30

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!!

@Paul Newcome @James Keuning @Andrée Starå

Paul Newcome

Try your first formula idea where you used COUNTIF() + COUNTIF() + COUNTIF() but use a series of COUNTIFS instead so that you can use a range/criteria set for the department, location, status, etc. all within each COUNTIFS.