Need some help with a formula that doesn't seem to be working. For context, here is a description of the sheet setup and formulas I'm using (+ I've attached an image with screenshots of all the pieces as a visual aid).
The sheet I'm working on is our RAID log template that we have built into our control center solution.
We have a Type column to tag whether something is a risk, issue, action item, or decision. We have a column to indicate how critical a decision is (low, med, high, critical) and a date column to note when when a resolution is needed by.
I've added two helper columns to the end of the sheet. The Critical Decision Aging column is there to calculate how many days PAST the resolution date we've gone for decisions that are not marked as complete. The Aging Groups column then uses the aging data to put these not complete decisions into one of through "groups" (for charting purposes, and the groups are Not Past Due, < 2 Weeks Past Due, and > 2 Weeks Past Due).
Here are the formulas I used for these two helper columns (which appear to be working exactly as I want them to, as you will see in the attached screenshot):
Critical Decision Aging Helper Column Formula
=IF([Sheet Config]@row = 1, " ", IF(Type@row = "Decision", IF(OR([Decision Criticality]@row = "High", [Decision Criticality]@row = "Critical"), IF(Status@row <> "Complete", NETWORKDAYS([Needs to be Resolved by]@row , TODAY()), ""))))
Aging Groups Helper Column Formula
=IF(AND(Type@row = "Decision", Status@row <> "Complete", [Critical Decision Aging]@row <= 0), "Not Past Due", IF(AND(Type@row = "Decision", Status@row <> "Complete", [Critical Decision Aging]@row > 14), "> 2 Weeks Past Due", IF(AND(Type@row = "Decision", Status@row <> "Complete", [Critical Decision Aging]@row > 0, [Critical Decision Aging]@row <= 14), "< 2 Weeks Past Due", "N/A")))
Then I've ALSO added 3 rows to the Summary section of the sheet so I can count the number of not complete decisions in each of the 3 groups and push that data into our Portfolio Summary Rollup. The purpose of this is to a.) include these counts for each program in the All Active Programs report we show on a leadership dashboard and b.) be able to product a chart of this same data for the full portfolio. I was thinking the formulas for these would be very straightforward COUNTIFS formulas, but only 1 of the 3 is counting correctly. See gray screenshot in my attached image - the 3 COUNTIFS rows are circled in pink. Here are the formulas I'm using for each:
Open Critical Design Aging - Not Past Due (only this one is calculating correctly and giving me a count of 1)
=COUNTIFS([Aging Groups]:[Aging Groups], "Not Past Due", [Sheet Config]:[Sheet Config], 0)
Open Critical Design Aging - < 2 Weeks Past Due (this one is giving me a count of 0 when it should be 1 according to the data in the sheet)
=COUNTIFS([Aging Groups]:[Aging Groups], "< 2 Weeks Past Due", [Sheet Config]:[Sheet Config], 0)
Open Critical Decision Aging - > 2 Weeks Past Due (this one is giving me a count of 9 when it should be giving me a count of 2 according to the data in the sheet…and 9 just so happens to be the total # of populated RAID rows on the sheet)
=COUNTIFS([Aging Groups]:[Aging Groups], "> 2 Weeks Past Due", [Sheet Config]:[Sheet Config], 0)
Note: the [Sheet Config]:[Sheet Config], 0 syntax is in there so it won't try and count any of the control center related rows at the top of the sheet.
I would greatly appreciate any guidance on why the 2nd two COUNTIFS formulas are not counting properly (one is counting NONE of the rows and one is counting ALL of the rows…). Thank you in advance!
