Compare range of dates and sum...

[SAL]

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

 

[JE McGimpsey]

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$100>B2),$D$2:$D$100)

 

[SAL]

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$100>B2),$D$2:$D$100)





.

 

[JE McGimpsey]

Using your data I get

E2: 167
E3: 227
E4: 227
E5: 171

Are your values numbers or text? (Try putting =ISTEXT(D2) in another
cell).

Are your dates real XL dates? or are they text?

 

[SAL]

Thanks, it worked. I wasn't using the "--" what is the
purpose of the "--" in the formula?

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/doubleneg.html