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 |
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 |
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 |
All times are GMT +1. The time now is 07:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com