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.
Bookmarks