Is there a way to calulate M-T-D or Y-T-D in Excelt?

B

bdsirius

Data is being entered on a daily basis into Excel. Each day the total for
the month or year needs to be summed.
 
R

Ragdyer

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),"")
 
R

Roger Govier

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
 
Top