Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for date selection
To summarise my earlier post: I have a list of dates, each with a
corresponding value, and my aim is to add up the values that correspond to each month. The dates are not necessarily in order. I had a very helpful response last time which allowed me to add up all the January values, all the February values etc, using the formula =SUMPRODUCT((MONTH($A$1:$A$100)=1)*(B1:B100)) which checks cells A1:A100 and if the month is January (1) adds the values in column B. Now this is great, but now I have the added problem of needing to select a month from a particular year i.e. January 2007 and January 2008 need to be totalled seperately. I'm sure there must be a similar way to do this but if not I'll have to resort to using the Julian date format and use range tables, which I'd rather not have to do! Thanks to anyone who is able to help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for date selection
=SUMPRODUCT((MONTH($A$1:$A$100)=1)*(Year($A$1:$A$1 00)=2007)*(B1:B100))
"glitterjen" wrote: To summarise my earlier post: I have a list of dates, each with a corresponding value, and my aim is to add up the values that correspond to each month. The dates are not necessarily in order. I had a very helpful response last time which allowed me to add up all the January values, all the February values etc, using the formula =SUMPRODUCT((MONTH($A$1:$A$100)=1)*(B1:B100)) which checks cells A1:A100 and if the month is January (1) adds the values in column B. Now this is great, but now I have the added problem of needing to select a month from a particular year i.e. January 2007 and January 2008 need to be totalled seperately. I'm sure there must be a similar way to do this but if not I'll have to resort to using the Julian date format and use range tables, which I'd rather not have to do! Thanks to anyone who is able to help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for date selection
Try this:
=SUMPRODUCT((MONTH($A$1:$A$100)=1)*(YEAR($A$1:$A$1 00)=2007)*(B1:B100)) or, rather than hardcode the search values within the formula, better to put your month in one cell (X1) and the year in another (X2), like so: =SUMPRODUCT((MONTH($A$1:$A$100)=X1)*(YEAR($A$1:$A$ 100)=X2)*(B1:B100)) Hopr this helps. Pete On Jul 25, 10:12 am, glitterjen wrote: To summarise my earlier post: I have a list of dates, each with a corresponding value, and my aim is to add up the values that correspond to each month. The dates are not necessarily in order. I had a very helpful response last time which allowed me to add up all the January values, all the February values etc, using the formula =SUMPRODUCT((MONTH($A$1:$A$100)=1)*(B1:B100)) which checks cells A1:A100 and if the month is January (1) adds the values in column B. Now this is great, but now I have the added problem of needing to select a month from a particular year i.e. January 2007 and January 2008 need to be totalled seperately. I'm sure there must be a similar way to do this but if not I'll have to resort to using the Julian date format and use range tables, which I'd rather not have to do! Thanks to anyone who is able to help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dsum date based on selection of months | Excel Worksheet Functions | |||
selection based on a date in a cell | Excel Discussion (Misc queries) | |||
Can I set up a calendar lookup for date selection | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Date Selection for Pivot Table | Excel Discussion (Misc queries) |