Why not simplify matters?

Starting date in C1 of Sheet1,
Ending date in C2 of Sheet1,
Range on Sheet2:
A2 to A100 - dates (in any order)
B2 to B100 - values

Try this formula:

=SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<= C2)*Sheet2!B2:B100)/
SUMPRODUCT((Sheet2!A2:A100=C1)*(Sheet2!A2:A100<=C 2))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Chap" wrote in message
...
Sorry, I didn't quite get it.
What would be the named ranges? ChecksA and ChecksD? For the dates and for
the target values? And the Date1 and Date2 values I mentioned, where would
they come in? As R2 and S2?


"Don Guillett" wrote:

Assumes named ranges and valid dates. An array formula that must be
entered
using ctrl + shift +enter

=AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chap" wrote in message
...
In my workbook, one worksheet (Tab1) should contain several formulas
structured as:

=AVERAGE('Tab2'!Address1:Address2)

The other worksheet (Tab2) contains a table, in which column A are all
dates
from the last 5 years, in ascending order, and column B the
corresponding
target values.

Address1 and Address2 are the cells in column B of Tab2 located on the
same
rows as the cells in column A that contain Date1 and Date2,
respectively.

Date1 and Date2 are cells (containing dates) in Tab1.

My goal is to have each formula in Tab1, given Date1 and Date2,
calculate
an
average of target values retrieved from the table in Tab2.

How should I complete the formulas? Should a Lookup function be used?

Many thanks.
Chap