+ Reply to Thread
Results 1 to 7 of 7

Count Unique Task IDs Completed within a Date Range

  1. #1
    Registered User
    Join Date
    12-09-2011
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    4

    Count Unique Task IDs Completed within a Date Range

    First post here so please forgive me if I forget to provide info needed for my problem. I have an Excel 2007 .xlsx with a worksheet (Sheet1) contining many rows of project task info in multiple columns and I need to get the count of unique TaskIDs that have a TaskCompletionDate that falls between two specific dates. My problem is that the list contains numerous duplicate TaskIDs so I don't know what to do to only count the unique ones.

    Sheet1 contains 639 rows of project-related info in numerous columns like ProjectID, TaskID, SubtaskID, TaskCompletionDate, etc. along other project, task, and subtask info. TaskID is in column T and TaskCompletionDate is in column AA.

    Sheet2 contains 13 rows, one for each quarter in 2010, 2011, and 2012 (and a header row) where column A is the first date of a quarter and column B is the last date of the quarter (e.g. A2=1/1/2010, B2=3/31/2010, A3=4/1/2010, B3=6/30/2010, etc.). In column C next to the two dates I need the count of unique TaskIDs from Sheet1 where the TaskCompletionDate falls between the two dates listed on Sheet2.

    I found a similar post here solved with SUMPRODUCT but I don't know how to tweak it to only include the unique TaskIDs. Is the solution to my problem as simple as tweaking the following SUMPRODUCT function with some sort of FREQUENCY function added to it or do I need to do something completely different?

    =SUMPRODUCT(--(Sheet1!$AA$2:$AA$639>=Sheet2!A2),--(Sheet1!$AA$2:$AA$639<=Sheet2!B2),--(Sheet1!$AA$2:$AA$639=Sheet1!$AA$2:$AA$639))

    Any help here would be greatly appreciated. Thanks.
    Last edited by Spoklahoma; 12-11-2011 at 03:58 PM.

  2. #2
    Registered User
    Join Date
    12-11-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Count Unique Task IDs Completed within a Date Range

    Hi,

    I'm not sure if I understood your problem correctly but based on what you've mentioned, I think you have a problem with multiple task ID's due to incorrect data entry. To be more specific same entries have been made twice or more, like a task ID xxxx with a completion date of mmddyyyy has been entered more than once.

    Now when you are trying to take a count of ID's based of completion date it is not giving you correct values due to duplicate entries.

    If this is your problem, to fix this you need to use advance filter option. This is to copy your unique task ID's. Once you have unique ID's you can do a vlookup to get your completion dates and then do a countif.

    I've attached an excel file detailing the steps you need to follow. Hope that helps...have a great day!!

    Warm regards
    Ishtiyaq
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-09-2011
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Count Unique Task IDs Completed within a Date Range

    Thanks, but I don't think I explained my problem fully. You see, Sheet1 contains a list of Projects, where each Project has mulitple Tasks (with TaskIDs) and some Tasks have multiple Subtasks, while other Tasks have zero Subtasks, hence why I have duplicate TaskIDs. Think of the Subtasks as milestones in a project so you can more accurately track progress. As time goes on I'll have several thousand rows and I want to avoid having to first sort/filter the list and then do my count; I'd rather just use a function to do this to save time.

    Imagine you have a list of projects is associated with getting dressed. The project "Put on your shoes" may need to be broken into two Tasks (one for putting on your left shoe, and one for putting on your right shoe), which you may want to break out even further into Subtasks for putting your foot into the shoe, pulling the laces tight, and tying the laces. There will be duplicate ProjectIDs and TaskIDs because some Tasks will have multiple Subtask, while other Tasks might not have any Subtasks. So, if I wanted to write a formula for seeing the unique TaskIDs that were completed between "x" and "y" dates, how would I do this?

    I'm not sure how to attach a document in this post so here's a made up example with 10 rows and 12 columns where you can see duplicate TaskIDs in column E which is what I want to find the unique count of that had a Task_Completion_Date (column H) between 2 dates of my choosing.

    A B C D E F G H I J K L
    1 ProjectID Project_Description Project_Target_Date Project_Completion_Date TaskID Task_Description Task_Target_Date Task_Completion_Date SubtaskID Subtask_Description Subtask_Target_Date Subtask_Completion_Date
    2 100292 Put on your shoes. 1/30/11 1/28/11 100333 Put on your left shoe. 1/15/10 1/15/10 500804 Put your left foot toes into the left shoe. 1/11/11 1/11/11
    3 100292 Put on your shoes. 1/30/11 1/28/11 100333 Put on your left shoe. 1/15/10 1/15/10 500805 Put your left foot heel into the left shoe. 1/11/11 1/11/11
    4 100292 Put on your shoes. 1/30/11 1/28/11 100333 Put on your left shoe. 1/15/10 1/15/10 500806 Pull the laces on the left shoe tight. 1/11/11 1/11/11
    5 100292 Put on your shoes. 1/30/11 1/28/11 100333 Put on your left shoe. 1/15/10 1/15/10 500807 Tie the laces on the left shoe. 1/11/11 1/11/11
    6 100292 Put on your shoes. 1/30/11 1/28/11 100334 Put on your right shoe. 1/16/10 1/16/10 500808 Put your right foot toes into the right shoe. 1/14/11 1/14/11
    7 100292 Put on your shoes. 1/30/11 1/28/11 100334 Put on your right shoe. 1/16/10 1/16/10 500809 Put your right foot heel into the right shoe. 1/14/11 1/14/11
    8 100292 Put on your shoes. 1/30/11 1/28/11 100334 Put on your right shoe. 1/16/10 1/16/10 500810 Pull the laces on the right shoe tight. 1/14/11 1/14/11
    9 100292 Put on your shoes. 1/30/11 1/28/11 100334 Put on your right shoe. 1/16/10 1/16/10 500811 Tie the laces on the right shoe. 1/14/11 1/14/11
    10 100293 Put on your hat. 2/14/11 2/11/11 100335 Place your hat on your head. 2/14/11 2/11/11

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Count Unique Task IDs Completed within a Date Range

    Assume Sheet2 col_T contains the Task ID's & Col_AA contains the dates. Try this ARRAY FORMULA in Sheet1!C2,(must hit CTRL+SHIFT+ENTER, rather than just ENTER ) then copy down.

    =SUM(SIGN(FREQUENCY(IFERROR(IF(Sheet1!AA$2:AA$636>=$A2,IF(Sheet1!AA$2:AA$636<=B2,MATCH(Sheet1!T$2:T$636,Sheet1!T$2:T$636,0))),""),ROW(Sheet1!T$2:T$636)-ROW(Sheet1!T$2)+1)))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    12-09-2011
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Count Unique Task IDs Completed within a Date Range

    SUCCESS!!!! Haseeb, thank you so very much. Worked like a charm.

  6. #6
    Registered User
    Join Date
    12-09-2011
    Location
    Connecticut
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Count Unique Task IDs Completed within a Date Range

    Sorry, but how do I mark this thread as SOLVED?

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Count Unique Task IDs Completed within a Date Range

    To mark your thread solved do the following:
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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