Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. . |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|