Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ytd totals for multiple years
Hi,
In column A i have the month formatted as mmm-yy In colums B, C, D,E and F I have values for different categories. In some cells, starting at P4 for example. I would like to get YTD totals for columns B,C etc. I would like to be able to simply enter my values in B,C, etc and P4 updates such that when I enter the values for Jan of a new year the YTD total starts over by itself. Any ideas? Thanks for the time and effort. -Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ytd totals for multiple years
This formula works on the basis that you don't input a date in column A until
you have data in the related columns. =SUMPRODUCT((YEAR(A2:A200)=YEAR(MAX(A2:A200)))*B2: B200) Note that SUMPRODUCT can not call out the entire column, unless using XL 2007. Also, an alternative setup, if you wish, would be to replace "YEAR(MAX(A2:A200))" with a cell reference in which you simply place the year you want data for. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MH" wrote: Hi, In column A i have the month formatted as mmm-yy In colums B, C, D,E and F I have values for different categories. In some cells, starting at P4 for example. I would like to get YTD totals for columns B,C etc. I would like to be able to simply enter my values in B,C, etc and P4 updates such that when I enter the values for Jan of a new year the YTD total starts over by itself. Any ideas? Thanks for the time and effort. -Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ytd totals for multiple years
Thanks,
Worked "Luke M" wrote: This formula works on the basis that you don't input a date in column A until you have data in the related columns. =SUMPRODUCT((YEAR(A2:A200)=YEAR(MAX(A2:A200)))*B2: B200) Note that SUMPRODUCT can not call out the entire column, unless using XL 2007. Also, an alternative setup, if you wish, would be to replace "YEAR(MAX(A2:A200))" with a cell reference in which you simply place the year you want data for. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MH" wrote: Hi, In column A i have the month formatted as mmm-yy In colums B, C, D,E and F I have values for different categories. In some cells, starting at P4 for example. I would like to get YTD totals for columns B,C etc. I would like to be able to simply enter my values in B,C, etc and P4 updates such that when I enter the values for Jan of a new year the YTD total starts over by itself. Any ideas? Thanks for the time and effort. -Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to link multiple columns to multiple worksheet totals / functi | Excel Worksheet Functions | |||
Creating stacked bar for multiple groups and multiple years | Charts and Charting in Excel | |||
same month/day on x axis for multiple years | Charts and Charting in Excel | |||
synchronizing the years for multiple dates | Excel Discussion (Misc queries) | |||
synchronizing the years for multiple dates | Excel Discussion (Misc queries) |