ytd totals for multiple years

[MH]

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

 

[Luke M]

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.

 

[MH]

Thanks,
Worked

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!*