All I'm getting are zeros.
-----Original Message-----
Perhaps I'm misunderstanding you, but I don't see why E3
& E4 (shipments
#2 & #3) aren't $227, since the payment terms overlap
all the other
shipments.
If that's correct, then try:
E2: =SUMPRODUCT(--($B$2:$B$100<C2),--
(C$2:$C$100B2),$D$2:$D$100)
In article ,
"SAL" wrote:
I have a spreadsheet that lists shipment number (in
column
a), estimated shipping dates (in column b) estimated
payment due dates (in column c) which are usually 45
days
after the shipping date. Each shipment has a total
dollar
amount (which is column d) and in the next column I
would
like the spreadsheet to automatically capture the total
dollar amount for the all the shipments that will
remain
unpaid during the payment term of a particular shipment
number. For example:
Ship.# Ship-Date Due-Date $Amt Total-Outstanding$
1 4/12 5/27 56 167
2 5/25 7/09 72 167
3 4/17 6/01 39 167
4 5/28 7/12 60 171
Any overlap of the payment term period of any rows with
a
shipment number above or any rows below with a shipment
number need to be compared and sum the individual
shipment
$ amounts in column (e) where overlap exists.
Thanks,
SAL
.
|