Is there a way to calulate M-T-D or Y-T-D in Excelt?
Data is being entered on a daily basis into Excel. Each day the total for
the month or year needs to be summed. |
Is there a way to calulate M-T-D or Y-T-D in Excelt?
Column labels in Row1.
Dates in Column A. Data in Column B, from B2 to B366 Enter this formula in C2, and copy down to C366: =IF(B2,SUM($B$2:B2),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bdsirius" wrote in message ... Data is being entered on a daily basis into Excel. Each day the total for the month or year needs to be summed. |
Is there a way to calulate M-T-D or Y-T-D in Excelt?
Hi
With your dates in column A and values in column B and assuming that row 1 contains headers then set up a series of dates in say column F2:F14 with dates for the Year in question e.g. 1/1/05, 1/2/05, .... 1/12/05 (note these are UK style dates, change to date format for your locality). Put a heading in G1 Month Sales, in H1 Year to Date Sales in G2 =SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH($F2)),$B$2:$B$1000) copy down through G3:G14 In H2 =SUMPRODUCT(--(YEAR($A$2:$A$1000)=YEAR($F2)),$B$2:$B$1000) If you only wanted data for the year to date and the current month to date, then for the month value use =SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(TODAY())),$B$2:$B$1000) Regards Roger Govier bdsirius wrote: Data is being entered on a daily basis into Excel. Each day the total for the month or year needs to be summed. |
All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com