If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dollar amount dependent on date
Hi, I have a column of $ amounts and corresponding dates
in another column. Date Amount 1/1/04 $100.00 1/15/04 $100.00 1/31/04 $100.00 On a seperate page I have Sum for Month January $200.00 February $100.00 If the date occurs up to and including the 15th of the month I want to be able to add that $ amount into a cell which would contain the entire sum for the month. If the date occurs after the 15th it would need to be added to the sum of the next month. |
#2
|
|||
|
|||
something like this?
=sumproduct((month(a2:a200)=1)*(day(a2:a200)=15)* b2:b200) -- Don Guillett SalesAid Software wrote in message ... Hi, I have a column of $ amounts and corresponding dates in another column. Date Amount 1/1/04 $100.00 1/15/04 $100.00 1/31/04 $100.00 On a seperate page I have Sum for Month January $200.00 February $100.00 If the date occurs up to and including the 15th of the month I want to be able to add that $ amount into a cell which would contain the entire sum for the month. If the date occurs after the 15th it would need to be added to the sum of the next month. |
#3
|
|||
|
|||
Hi
try for February: =SUMPRODUCT(--('sheet1'!A1:A100DATE(2004,1,15)),--('sheet1'!A1:A100=D ATE(2004,2,15)),'sheet1'!B1.B100) -- Regards Frank Kabel Frankfurt, Germany wrote: Hi, I have a column of $ amounts and corresponding dates in another column. Date Amount 1/1/04 $100.00 1/15/04 $100.00 1/31/04 $100.00 On a seperate page I have Sum for Month January $200.00 February $100.00 If the date occurs up to and including the 15th of the month I want to be able to add that $ amount into a cell which would contain the entire sum for the month. If the date occurs after the 15th it would need to be added to the sum of the next month. |
#4
|
|||
|
|||
Worked! Thanks
-----Original Message----- Hi try for February: =SUMPRODUCT(--('sheet1'!A1:A100DATE(2004,1,15)),-- ('sheet1'!A1:A100=D ATE(2004,2,15)),'sheet1'!B1.B100) -- Regards Frank Kabel Frankfurt, Germany wrote: Hi, I have a column of $ amounts and corresponding dates in another column. Date Amount 1/1/04 $100.00 1/15/04 $100.00 1/31/04 $100.00 On a seperate page I have Sum for Month January $200.00 February $100.00 If the date occurs up to and including the 15th of the month I want to be able to add that $ amount into a cell which would contain the entire sum for the month. If the date occurs after the 15th it would need to be added to the sum of the next month. . |
#5
|
|||
|
|||
Worked! Thanks
-----Original Message----- Hi try for February: =SUMPRODUCT(--('sheet1'!A1:A100DATE(2004,1,15)),-- ('sheet1'!A1:A100=D ATE(2004,2,15)),'sheet1'!B1.B100) -- Regards Frank Kabel Frankfurt, Germany wrote: Hi, I have a column of $ amounts and corresponding dates in another column. Date Amount 1/1/04 $100.00 1/15/04 $100.00 1/31/04 $100.00 On a seperate page I have Sum for Month January $200.00 February $100.00 If the date occurs up to and including the 15th of the month I want to be able to add that $ amount into a cell which would contain the entire sum for the month. If the date occurs after the 15th it would need to be added to the sum of the next month. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Evaluating values in cells (an Array?) and returning a dollar amount | Beth | Worksheet Functions | 1 | August 17th, 2004 09:02 PM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
If statement | Doug | Worksheet Functions | 9 | June 28th, 2004 06:13 AM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |