On 2002-04-15 13:57, Big Blue wrote:
{=SUM((salesbase2!$E$2:$E$1000>='02 Holidays'!$A13)*(salesbase2!$E$2:$E$1000<='02 Holidays'!$B13))}
col E is dates in ddmmyy format, as is
"02 Holidays' which is a 2 column worksheet of start/end dates for every week in 2002
I have named E:E as DATEIN and want to use it to replace salesbase2!$E$2:$E$1000
this formula effectively subtotals a count of log entries by work week.
Array formulas do not accept whole columns as range arguments. DATEIN is E:E, so it cannot be used in your array formula.
May I propose a different scheme, which allows the relevant range in E to change by additions or deletions.
I'll assume that both worksheets
salebase2 and
02 Holidays are in the same workbook.
Activate
salebase2.
Activate the option Insert|Name|Define.
Enter as name
DateRecs in the Names in Workbook box.
Enter as formula in the Refers to box:
=MATCH(9.99999999999999E+307,salebase2!$E:$E)
Activate Add (don't leave the Define Name window yet).
Enter as name
DATEIN in (or select it if already available from) the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(salebase2!$E$2,0,0,DateRecs-1,1)
Activate OK.
Now go to the worksheet
02 Holidays and in C13 enter either your array formula modified as
{=SUM((DATEIN>=$A13)*(DATEIN<=$B13))}
or, ordinarily entered,
=SUMPRODUCT((DATEIN>=$A13)*(DATEIN<=$B13))
However, if dates in A from A13 on and in B from B13 on are dates that define effectively calendar weeks, I'd suggest replacing them by a single range in A from A3 on where you create the 52 week numbers and using the following formula in B13:
=SUMPRODUCT((WEEKNUM(DATEIN)=$A13)+0)
Note. WEEKNUM is available from Analysis Toolpak (which is available thru Tools|Add-Ins).
Aladin