I am trying to create a spreadsheet to track the # of projects in flight by project stage (Evaluate, Initiate, Plan/Design , Execute, Close) and map dependencies like data, integration, security, etc. Attached is a simple model of the larger spreadsheet. What I am trying to do is write a function for cells C18:P20 that allows me to count the number of projects involving “data” (from column Q) to projects in either Plan/Design (P) or Execute (EX) stages within columns C-P (months of the year). I wrote the function below and it works as intended, but when I filter the spreadsheet by column B (portfolio and totals) I want cells C18:P20 to reflect the sum of the filtered data vs the entire data set.
=SUM(COUNTIFS($Q2:$Q12,"*",C2:C12,{"P","EX"}))
I am using the SUMPRODUCT function in cells C13:C17 to work in a similar manner, but I can’t seem to find a way to map the “data” from column Q using SUMPRODUCT
Any help or thoughts would be appreciated.
-B
Bookmarks