# Thread: Match dates to select data (2000/SP3)

1. ## Match dates to select data (2000/SP3)

I am looking for some assistance on selecting data based on a match between two dates.
A master worksheet has mmm-yy column headings and a row for each project being worked. Each project has a separate worksheet which lists, among other things, a column of milestone due dates (dd/mm/yy format) and a column of my expected income due on those dates.

I want the master worksheet to show the expected income for the project when the mmm-yy column heading matches the milestone date. I have tried all sorts of date functions and lookups but can't get it working. Any clues please?? A sample worksheet is attached.

Alex

2. ## Re: Match dates to select data (2000/SP3)

Select T2:CB2 on the REVENUE RECORD AND FORECAST sheet. Make sure that T2 is the active cell within the selection. Enter this formula:

=SUMPRODUCT((DATE(YEAR('Project A'!\$D\$3:\$D\$8),MONTH('Project A'!\$D\$3:\$D\$8),1)=T\$1)*'Project A'!\$F\$3:\$F\$8)

Confirm with Ctrl+Enter. (You can also select T2, enter the formula, then fill to the right.) The formula takes D38 on the Project A worksheet, calculates the first day of the month and compares that to T1. This results in a series of TRUE/FALSE values. Since TRUE = 1 and FALSE = 0, multiplying withF3:F8 and summing results in adding only the values with a matching date.

3. ## Re: Match dates to select data (2000/SP3)

Great work, thanks Hans. That gives me an introduction to a function (sumproduct) I have never used before.

Regards
Alex

#### Posting Permissions

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