COUNTIFS formula on past due Dates with conditions

[rz6657]

In the attached file, in the TechDev tab, I have the resources names and the status of the projects they own. Than to the far right, all the milestones that align with each project - planned and actual (2 columns per milestone). In the Workload tab, I want to be able to do the following:

1. Under the Past Due column per milestone, count how many milestones are past due based on TODAY, where resource is Resource 1 (or 2, 3, so on) and status is In Progress

2. In the Due In column per milestone, although a count is fine, I was wondering if a formula can quantify how many days more before it is actually past due using the Planned column.



Thx

[tim201110]

=SUM(($D8=TechDev!$G$7:$G$118)*($D$7=TechDev!$M$7:$M$118)*(E$6=TechDev!$AS$6:$BN$6)*(TechDev!$AS$7:$BN$118>0)*(TechDev!$AS$7:$BN$118<=$D$6)) CSE
=IFERROR(AGGREGATE(15,6,TechDev!$AS$7:$BN$118/($D8=TechDev!$G$7:$G$118)/($D$7=TechDev!$M$7:$M$118)/(E$6=TechDev!$AS$6:$BN$6)/(TechDev!$AS$7:$BN$118>$D$6),1)-$D$6,"")

[rz6657]

I forgot to mention to count only those where the _ACT is empty. If it's not empty, it met the milestone. If it's empty, the milestone has not been met and is past due... thx

[tim201110]

=SUM(($D8=TechDev!$G$7:$G$118)*($D$7=TechDev!$M$7:$M$118)*(E$6=TechDev!$AS$6:$BN$6)*(TechDev!$AS$7:$BN$118>0)*(TechDev!$AS$7:$BN$118<=$D$6)*(INDEX(TechDev!$AS$7:$BN$118,,MATCH(E$6,TechDev!$AS$6:$BN$6,)+1)=0)) CSE

[rz6657]

It's not working Tim -- not sure why... I did activate the array ---

[tim201110]

this is better
=SUM(($D8=TechDev!$G$7:$G$118)*($D$7=TechDev!$M$7:$M$118)*(INDEX(TechDev!$AS$7:$BN$118,,MATCH(E$6,TechDev!$AS$6:$BN$6,))>0)*(INDEX(TechDev!$AS$7:$BN$118,,MATCH(E$6,TechDev!$AS$6:$BN$6,))<=$D$6)*(INDEX(TechDev!$AS$7:$BN$118,,MATCH(E$6,TechDev!$AS$6:$BN$6,)+1)=0))

[rz6657]

For some reason it's not working for me. And it counts the ones that are not past due yet... I also tried it on the file you attached and it's not working either. any Ideas? Thx

[tim201110]

more efficient formula
excel is very strict with data. some cells are (blue) not empty

[rz6657]

It worked perfectly; thanks so much for being patient and persistent tim201110. You've made my work more efficient...