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.

RSmith30

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

Thank you for the suggestion on the approach/strategy. I think I am close but keep receiving an invalid operation error and can't seem to pinpoint it.

=SUM(COUNTIFS({CI Activity Master Sheet Status}, "In Progress", {CI Activity Master Sheet Location}, {"Dept. 9-10", "Dept. 9-11", "Dept. 9-12", "Dept. 2-21", "Dept. 2-23", "Dept. 2-24", "CLE-Electronics Factory", "Machine Division"}))

"CI Activity Master Sheet Status" and "CI Activity Master Sheet Location" are the columns referenced on a different sheet.

"In progress" is the first criteria to count and all the departments listed is the second criteria.

Paul Newcome

No. You would work it similar to your first formula where you have

=COUNTIF() + COUNTIF() + COUNTIF()

but instead you would use a series of COUNTIFS so that you can include multiple range/criteria sets within each.

=COUNTIFS() + COUNTIFS() + COUNTIFS()