a Month to Date and Year TD page in one sheet changing each day?

Can a single Excel worksheet be created to calculate month-to-date and
year-to-date with input on a daily basis, changing the daily input, but
keeping the previous values for MTD and YTD?

Hi Mark

Not sure if I fully understand your request, but assuming you wanted to
see the totals up to yesterday, whilst inputting today's values then
this can be achieved fairly easily. Obviously, as soon as you open the
sheet tomorrow, values entered today would be included, but then not the
values being input during the day again

With Dates in Column A, starting at row 2, and values in column B,
for Month to date enter in cell C1
=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(TODAY())),
--($A$2:$A$1000<=--TODAY())-1),$B$2:$B$1000)

For Year to Date enter in cell D1
SUMPRODUCT(--($A$2:$A$1000>=DATE(YEAR(TODAY()),1,1)),
--($A$2:$A$1000<=--TODAY()-1),$B$2:$B$1000)

Change the ranges to suit but ensure that they are of equal size.

--
Regards

Roger Govier


"Mark A. Fresh" <Mark A. Fresh@discussions.microsoft.com> wrote in
message news:3DCACBB9-0623-4D8B-8FC5-DFEE710D4459@microsoft.com...
> Can a single Excel worksheet be created to calculate month-to-date and
> year-to-date with input on a daily basis, changing the daily input,
> but
> keeping the previous values for MTD and YTD?