Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula based on dates
I would like to write a formula that adds numbers between certain dates.
Lets say I have this worksheet: A B 01-01-08 5 01-02-08 7 ....... 11-28-08 3 11-29-08 11 In another worksheet, I would like to be able to type in two dates , and have excel look back at the original worksheet and add all the values in column B between the two dates listed. Is that possible? The date ranges will be added anyway, so using them to do the formula would be much easier than going back and highlighting the data range every time. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula based on dates
On Sat, 29 Nov 2008 11:04:01 -0800, filups
wrote: I would like to write a formula that adds numbers between certain dates. Lets say I have this worksheet: A B 01-01-08 5 01-02-08 7 ...... 11-28-08 3 11-29-08 11 In another worksheet, I would like to be able to type in two dates , and have excel look back at the original worksheet and add all the values in column B between the two dates listed. Is that possible? The date ranges will be added anyway, so using them to do the formula would be much easier than going back and highlighting the data range every time. If your start date is in cell D10 and your end date is in cell D11 you can try the following formula in cell to get the sum: =SUMPRODUCT((Sheet1!A1:A100=D10)*(Sheet1!A1:A100< =D11)*(Sheet1!B1:B100)) Change the 100 to fit the number of data that you have in the original worksheet, i.e. "Sheet1". Regarding "between": If you dont want to include the start date and/or end date, change = and <= to and < respectively. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula based on dates
try this
Sheet 1 has data ( col A dates & Col B numbers ) assumed sheet 2 cell A1 & B1 has date. in Cell C1 put this formula =SUMPRODUCT(--(sheet1!A1:A4=A1),--(sheet1!A1:A4<=B1)*(sheet1!B1:B4)) On Nov 30, 12:04*am, filups wrote: I would like to write a formula that adds numbers between certain dates. * Lets say I have this worksheet: * * * A * * * * * * * * * *B * * 01-01-08 * * * * * 5 01-02-08 * * * * * 7 ...... 11-28-08 * * * * * 3 11-29-08 * * * * *11 In another worksheet, I would like to be able to type in two dates , and have excel look back at the original worksheet and add all the values in column B between the two dates listed. *Is that possible? * The date ranges will be added anyway, so using them to do the formula would be much easier than going back and highlighting the data range every time.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula based on dates
Hi,
In 2007 =SUMIFS(Sheet1!B1:B18,Sheet1!A1:A18,"="&A1,Sheet1 !A1:A18,"<="&B1) In 2003 =SUMPRODUCT(Sheet1!B1:B18,--(Sheet1!A1:A18=A1),--(Sheet1!A1:A18<=B1)) If this helps, please click the Yes button Cheers, Shane Devenshire "filups" wrote: I would like to write a formula that adds numbers between certain dates. Lets say I have this worksheet: A B 01-01-08 5 01-02-08 7 ...... 11-28-08 3 11-29-08 11 In another worksheet, I would like to be able to type in two dates , and have excel look back at the original worksheet and add all the values in column B between the two dates listed. Is that possible? The date ranges will be added anyway, so using them to do the formula would be much easier than going back and highlighting the data range every time. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula based on dates
Hi,
You can also use either of the following: 1. DSUM function. Please read up on this in Excel's Help menu; 2. =SUM(IF((A2:A200=start_date)*(A2:A200<=start_date ),B2:B100)). This is an array formula - so please use a Ctrl+Shift+Enter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "filups" wrote in message ... I would like to write a formula that adds numbers between certain dates. Lets say I have this worksheet: A B 01-01-08 5 01-02-08 7 ...... 11-28-08 3 11-29-08 11 In another worksheet, I would like to be able to type in two dates , and have excel look back at the original worksheet and add all the values in column B between the two dates listed. Is that possible? The date ranges will be added anyway, so using them to do the formula would be much easier than going back and highlighting the data range every time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif formula based on dates | Excel Worksheet Functions | |||
Array formula based on text and dates.. | Excel Discussion (Misc queries) | |||
formula for specific dates based on another date in worksheet | Excel Discussion (Misc queries) | |||
Need help with a formula for calculating based on a rage of dates | Excel Discussion (Misc queries) | |||
formula to calculate a column based on dates in a different colum. | Excel Worksheet Functions |