The number of duplicate data in two lists

[avido83]

Hi,
I am working on a workbook to help me schedule vacation in my department. One thing that I am having trouble with is getting excel to return the number of days that exist in two different lists.

Example:

WEEK 1 REQUEST1
5/13/2012 5/10/2012
5/14/2012 5/11/2012
5/15/2012 5/12/2012
5/16/2012 5/13/2012
5/17/2012 5/14/2012
5/18/2012
5/19/2012

I would like excel to return 2, because two of the days in request1 fall within the range of week 1.

I know there has to be a simple way to get these results, but I have been hitting my head against a wall for a week trying to find it. Any help would be great!

 

[davesweep]

Hi,

Assuming your data starts at A1 with headers in row 1,

=SUMPRODUCT((B2:B8<=MAX(A2:A8))*(B2:B8>=MIN(A2:A8)))

 

[avido83]

Davesweep,
Thank you very much for your help. It works like a charm! I have read the sumproduct description on microsoft's website and I have no idea how it is working, but it does, perfectly!