The limit in 2003 is 1024 characters in a formula, shouldn't be any problem
with the length of the formula you provided. I was thinking of a
SUMPRODUCT() formula myself, but I couldn't get it worked out in my head this
evening, so I went with the brute force attack.

"Ron Rosenfeld" wrote:

On Thu, 28 Aug 2008 12:48:01 -0700 (PDT), ajnmx wrote:

I have a date range in columns A and B then a number in column C:


05-Jan-09 09-Feb-09 100.00
10-Feb-09 25-Mar-09 200.00
26-Mar-09 11-Apr-09 300.00


Then I have the 'input' section (rows 10 and 11), a further date
range:

Date From Date To
07-Feb-09 13-Feb-09
23-Mar-09 29-Mar-09

What I want to do is calculate the number of days that the date ranges
overlap, and multiply it by the value in column C.

So for example, the date range 7-Feb to 13-Feb overlaps the range 5-
Jan to 9-Feb by three days, and overlaps the range 10-Feb to 25-Mar by
four days. So the answer I'm looking for is 3x100 plus 4x200 = 1100.

There are rows and rows of this stuff, so I'm looking for a formula
that I can just copy down. I've been playing around with array
formulas but I can't get anything to work.

I've used this formula which works but obviously only for one line:

=IF(OR(($B10)<$A$1,$A10$B$1),0,(MIN(($B10),$B$ 1)-MAX($A10,$A
$1)+1))*C1

I thought I could turn this unto an array function like this:

=IF(OR(($B10)<($A$1:$A$3),$A10($B$1:$B$3)),0,(MI N(($B10),($B$1:$B$3))-
MAX($A10,($A$1:$A$3))+1))*(C1:C3)

...but it doesn't seem to work

Can anyone help?

I assumed your date ranges were in A1:B3.

Depending on how many date ranges you have, something like this should work:

=SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B1 0)),ROW(INDIRECT($A$1&":"&$B$1)),0))*$C$1)+
SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B10 )),ROW(INDIRECT($A$2&":"&$B$2)),0))*$C$2)+
SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B10 )),ROW(INDIRECT($A$3&":"&$B$3)),0))*$C$3)

There are some constraints on the allowable length of formula contents. In
Excel 2007, it is 8,192 characters. It might be less in earlier versions. But
if you run into that limitation, merely divide the formula into more than one
cell.
--ron