|
| 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
September 3rd, 2004, 05:15 PM
|
|||
|
|||
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
September 3rd, 2004, 05:21 PM
|
|||
|
|||
|
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
September 3rd, 2004, 05:22 PM
|
|||
|
|||
|
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
September 7th, 2004, 04:35 PM
|
|||
|
|||
|
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
September 7th, 2004, 04:35 PM
|
|||
|
|||
|
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 |
Linear Mode
