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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Data is being entered on a daily basis into Excel. Each day the total for
the month or year needs to be summed.
 
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),"")
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Adding up total hours worked on employee schedule using various criteria 0
Calculate interest & withdrawals 0
Force excel to read dates as D/M/YYYY 6
Importing Access DB into Excel changes my calculated fields in Exc 3
Is there a way to use labels and 3-D references in a formala? 2
I am trying to calulate total time 3
Calculating the Count of Monthly Gains/Losses 3

Back
Top