+ Reply to Thread
Results 1 to 9 of 9

COUNTIFS formula on past due Dates with conditions

  1. #1
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    357

    COUNTIFS formula on past due Dates with conditions

    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
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: COUNTIFS formula on past due Dates with conditions

    =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,"")
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    357

    Re: COUNTIFS formula on past due Dates with conditions

    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

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: COUNTIFS formula on past due Dates with conditions

    =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

  5. #5
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    357

    Re: COUNTIFS formula on past due Dates with conditions

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

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: COUNTIFS formula on past due Dates with conditions

    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))
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    357

    Re: COUNTIFS formula on past due Dates with conditions

    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
    Last edited by rz6657; 08-03-2018 at 02:55 PM.

  8. #8
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: COUNTIFS formula on past due Dates with conditions

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

  9. #9
    Forum Contributor
    Join Date
    08-15-2006
    Location
    USA
    MS-Off Ver
    Office 365, 64 bit
    Posts
    357

    Re: COUNTIFS formula on past due Dates with conditions

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. countifs, 2 conditions, dates and text match.
    By exceltriumph in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2017, 07:39 AM
  2. Countifs, 2 conditions (dates and text)
    By exceltriumph in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-26-2017, 07:12 AM
  3. COUNTIFS using Two Conditions Before Counting Dates
    By Aimee S. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2015, 01:07 PM
  4. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  5. VBA Formula for Past Due Dates
    By brwnrootsister in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2014, 07:14 AM
  6. [SOLVED] Error in COUNTIFS formula (3 conditions)
    By rvkadu1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-12-2013, 06:23 AM
  7. Formula for finding past dates
    By dmcfarland in forum Excel General
    Replies: 4
    Last Post: 04-11-2007, 12:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1