Looking up on 2 conditions, 1 being a date range

[smitha00]

Hi all,

I have been doing an assignment at work and have found that my life could be greatly simplified if I could just figure out one formula!

So here is the deal:

1. I have two workbooks. Work book A has a great amount of detail regarding companies, fees, leaders and estimated payment date. Workbook B is a summarized workbook where we list the company and when we expected to be paid from them.

2. Workbook A contains multiple duplicate companies that tell me for example:

Company Name Fee Amount Estimated Pmt Date
Comp. 1 $500 August 31, 2013
Comp. 1 $250 September 15, 2013
....
....
Comp. 2 50 September 15, 2013
Comp. 3 100 August 31, 2013


Workbook B looks like this:

Company Name July August September October November

Company 1 500 250
Company 2 50
Company 3 100

What I would like is a formula that matches by company name and month date. I can manually go through and do this, but it is quite the task.

I have tried an index function with a match formula involved and I have tried Vlookup. None of what I have tried can handle the date component of the formula I want since it needs to pick up all dates in August, September, etc.

Any thoughts? All help is very much appreciated!

[JBeaucaire]

Provide examples of the two workbooks, a small subset of the real data with sensitive data replaced by generic data. Make sure the "results" is manually mocked up from the sample data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[daffodil11]

Where A2 = Company 1
C1 = 7/1/2012, formatted to show only month
D1 = 8/1/2012, etc
etc

=SUMPRODUCT(('[WorkbookA.xlsx]Sheet1!'$A$1:$A$5000=$A2)*('[WorkbookA.xlsx]Sheet1!'$C$1:$C$5000>=$C1)*('[WorkbookA.xlsx]Sheet1!'$C$1:$C$5000<$D1)*('[WorkbookA.xlsx]Sheet1!'$B$1:$B$5000))

This finds all rows on workbook A that match workbook B's company in A2, then finds all dates that are >= current month, then all those less than next month, and on all those rows left over it totals the Column B.