Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totaling amounts based on duration
Hi,
I have three columns of data. What I am trying accomplish is totals from one of the columns based on the difference between dates, i.e. from the series of dates I would like to total the amounts where the invoices were paid within a 7, 14, 21, etc. days. I tried using a SUMIF statement using the dates as the range and "=7" as the criteria but continually get a zero value. invoice_date date_apid amount 11/9/2005 12/7/2005 200.00 10/13/2005 11/9/2005 406.48 10/13/2005 11/9/2005 512.79 10/26/2005 12/7/2005 500.00 11/30/2005 12/14/2005 500.00 9/8/2005 10/26/2005 104.31 10/3/2005 10/26/2005 104.31 12/1/2005 12/28/2005 104.31 9/5/2005 10/26/2005 3,053.00 9/4/2005 10/26/2005 1,688.00 8/21/2005 9/28/2005 1,680.00 8/21/2005 9/28/2005 1,688.00 9/11/2005 10/26/2005 1,239.00 9/11/2005 10/26/2005 1,603.60 9/18/2005 10/26/2005 1,680.00 9/18/2005 10/26/2005 1,848.00 9/25/2005 10/26/2005 1,848.00 9/28/2005 10/26/2005 1,491.00 9/25/2005 10/19/2005 1,584.00 10/2/2005 10/26/2005 1,680.00 10/2/2005 10/26/2005 1,848.00 10/2/2005 10/26/2005 2,640.00 9/15/2005 12/7/2005 80.00 I appreciate any help. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totaling amounts based on duration
=SUMPRODUCT(--($B$2:$B$200-$A$2:$A$200<=7),C2:C200)
gives 7 days etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message ups.com... Hi, I have three columns of data. What I am trying accomplish is totals from one of the columns based on the difference between dates, i.e. from the series of dates I would like to total the amounts where the invoices were paid within a 7, 14, 21, etc. days. I tried using a SUMIF statement using the dates as the range and "=7" as the criteria but continually get a zero value. invoice_date date_apid amount 11/9/2005 12/7/2005 200.00 10/13/2005 11/9/2005 406.48 10/13/2005 11/9/2005 512.79 10/26/2005 12/7/2005 500.00 11/30/2005 12/14/2005 500.00 9/8/2005 10/26/2005 104.31 10/3/2005 10/26/2005 104.31 12/1/2005 12/28/2005 104.31 9/5/2005 10/26/2005 3,053.00 9/4/2005 10/26/2005 1,688.00 8/21/2005 9/28/2005 1,680.00 8/21/2005 9/28/2005 1,688.00 9/11/2005 10/26/2005 1,239.00 9/11/2005 10/26/2005 1,603.60 9/18/2005 10/26/2005 1,680.00 9/18/2005 10/26/2005 1,848.00 9/25/2005 10/26/2005 1,848.00 9/28/2005 10/26/2005 1,491.00 9/25/2005 10/19/2005 1,584.00 10/2/2005 10/26/2005 1,680.00 10/2/2005 10/26/2005 1,848.00 10/2/2005 10/26/2005 2,640.00 9/15/2005 12/7/2005 80.00 I appreciate any help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie question Pulling data from one sheet to another based on | New Users to Excel | |||
Adding amounts based on a conditional date | Excel Discussion (Misc queries) | |||
SUM cells together based on 2 criteria | Excel Worksheet Functions | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |