Nel post:C7EE0DEB-39AC-45FC-8704-57FB7F15662A@microsoft.com,
Mike R. <MikeR@discussions.microsoft.com> ha scritto:

Hi Mike,

you could try something like this:

=SUMPRODUCT(($A$1:$A$20=E4)*($B$1:$B$20<=F4)*($C$1:$C$20))/SUMPRODUCT(($A$1:$A$20=E4))

where:

$A$1:$A$20 is the column for the names;

E4 is the cell with the recruiter you want to examin;

$B$1:$B$20 is the column for the dates;

F4 is the cell for the limit close date;

$C$1:$C$20 is the column for the # Days Open.

Try this array formula** :

F2 = recruiter
F3 = start date
F4 = end date

=AVERAGE(IF((Recruiter=F2)*(Close Date>=F3)*(Close Date<=F4),# Days Open))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Hi Mike,

Try using AutoFilter coupled with the Subtotal function. You then have a
dynamic worksheet where you can select any range of dates.

In pre xl2007 in AutoFilter you need to used Custom when setting the filter
and in xl2007 you can simply select the required dates.

Example of subtotal function is:-
=SUBTOTAL(1,B2:B18)
Where B2:B18 is the entire range of the unfiltered data.

You can get more information on the above in Help but post again if you have
problems.

Tip when using AutoFilter to make life easy:-

Set your column headers down from the top of the worksheet to about row 4
and then Freeze panes below the column headers and place your subtotal
function above the headers in the frozen area. You then never loose sight of
the column headers or your subtotal results.

Regards,

OssieMac