Compare range of dates and sum...
 

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

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

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
.

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?

In article ,
"SAL" wrote:

All I'm getting are zeros.

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

-----Original Message-----
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?

In article ,
"SAL" wrote:

All I'm getting are zeros.
.

See

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

In article ,
"SAL" wrote:

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