Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding in a Date Range
I have a worksheet with a Date Column. I want to include a formula that will
add, for example, amounts for the month of February 2008. How would I set that up? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding in a Date Range
Assume your dates are in column A and you want to add amounts from
column B - assume 1000 rows are used. To make it more flexible, put the date you are searching for in C1 (formatted as mmmm yyyy - the day doesn't matter), then put this formula in D1: =SUMPRODUCT((MONTH(A$1:A$1000)=MONTH(C1))*(YEAR(A$ 1:A $1000)=YEAR(C1))*(B$1:B$1000)) Hope this helps. Pete On Dec 17, 5:09 pm, readystate wrote: I have a worksheet with a Date Column. I want to include a formula that will add, for example, amounts for the month of February 2008. How would I set that up? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding in a Date Range
Hi,
When summing in this way it's a good idea to keep the date to sum outside of the formula so with 1/2/2008 in C1 the day of the monthe can be any =SUMPRODUCT(--(YEAR(A1:A20)=YEAR(C1)),--(MONTH(A1:A20)=MONTH(C1)),B1:B20) This will add up coolumn B for every Feb 2008 in column A1 - A20 Mike "readystate" wrote: I have a worksheet with a Date Column. I want to include a formula that will add, for example, amounts for the month of February 2008. How would I set that up? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding in a Date Range
Thanks...worked great!!!
"Mike H" wrote: Hi, When summing in this way it's a good idea to keep the date to sum outside of the formula so with 1/2/2008 in C1 the day of the monthe can be any =SUMPRODUCT(--(YEAR(A1:A20)=YEAR(C1)),--(MONTH(A1:A20)=MONTH(C1)),B1:B20) This will add up coolumn B for every Feb 2008 in column A1 - A20 Mike "readystate" wrote: I have a worksheet with a Date Column. I want to include a formula that will add, for example, amounts for the month of February 2008. How would I set that up? Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding in a Date Range
Hi
One way, assuming your dates are in column A and values in column B =SUMPRODUCT((TEXT($A$1:$A$1000,"yymm")="0802")*($B $1:$B$1000)) -- Regards Roger Govier "readystate" wrote in message ... I have a worksheet with a Date Column. I want to include a formula that will add, for example, amounts for the month of February 2008. How would I set that up? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a date to calender to automatially generate another date? | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
adding occurrences for date range | Excel Discussion (Misc queries) |