countif vlookup greater than date

[mkofler]

Greetings Excel Gods!
I'm trying to find out: For each person (ID #), how many times have they been arrested following a program referral? Each arrest is in a separate row:
For each ID number in Workbook1 (column B), search Workbook2 for each instance of that number. For each instance found in Workbook2, look at the date in column D. If the date in Workbook 2 column D is greater than the date in Workbook1 column AQ, then count it and return the total.

Any help would be greatly appreciated. I was thinking that a vlookup would work, but that only seems to find the first instance.
Thanks!

 

[just_jon]

Greetings Excel Gods!
I'm trying to find out: For each person (ID #), how many times have they been arrested following a program referral? Each arrest is in a separate row:
For each ID number in Workbook1 (column B), search Workbook2 for each instance of that number. For each instance found in Workbook2, look at the date in column D. If the date in Workbook 2 column D is greater than the date in Workbook1 column AQ, then count it and return the total.

Any help would be greatly appreciated. I was thinking that a vlookup would work, but that only seems to find the first instance.
Thanks!

=SUMPRODUCT(--('[WB2.xls]Sheetname'!$B$1:$B$1000=B1),--('[WB2.xls]Sheetname'!$D$1:$D$1000>AQ1))

Where workbook2 column B holds IDs, workbook2 column D holds dates.

 

[Legacy 51064]

I would use the sumproduct formula.

Put this in C1 and copy down

=SUMPRODUCT(('[Workbook2.xls]Sheet1'!$B$1:$B$5000=B1)*('[Workbook2.xls]Sheet1'!$D$1:$D$5000>=AQ1))


This is assuming that your ID on Workbook1 are in the B column.

 

[mkofler]

Sweet!
Thanks to both of y'all.
[Now uploading beer .........]