# Sum YTD values but also sum previous YTD as well

So I'm looking to sum the YTD sales through the current date by each customer in my list. But I have a second column for last years sales also and I want to sum the YTD sales for last year through the current date from last year. For example, I want to sum my YTD sales by customer through 2/19/16 and then in the column next to it I want to show the YTD sales through 2/19/15 so I can compare where we were last year through this date vs. this year through the same date. What would be the easiest formula to use to calculate this for this year and last year through the same month & day?
Microsoft Excel

Last Comment
Lawrence Salvucci

8/22/2022 - Mon
Professor J

can you upload a dummy example file so that i build formula in it?
Lawrence Salvucci

Here you go....So I left one customer in there on the Summary tab and also on the Detail tab. The detail tab is where you will get the sales for each month, etc. based on the Inv. Date column. On the summary tab I will need to show YTD sales for 2014, 2015, & 2016 through the current date. You will see each month with these headers under it. Since we are already in February then the January totals should be through the entire month of January. Then for February it would through the current date for each of the 3 years. The Forecast & % Delta columns will be used later so no need to worry about those columns.
Sample-File.xlsx
Rob Henson

Without a sample I would suggest use of DATE function to create an equivalent date to today for last year and first of year:

Last Year
=DATE(YEAR(TODAY()-1,MONTH(TODAY(),DAY(TODAY())

First Jan, last year
=DATE(YEAR(TODAY()-1,1,1)

First Jan, this year
=DATE(YEAR(TODAY(),1,1)

Replace the TODAY() parts with reference to a cell containing the date if required.

Then you can use SUMIFS function:

This Year:
=SUMIFS(SumRow,DateRow,">="&FirstJanDate,DateRow,"<="&TODAY())

Last Year:
=SUMIFS(SumRow,DateRow,">="&LastFirstJanDate,DateRow,"<="&LastYearDate())

Hope that makes sense.

This is assuming customer data already in rows. Could be adjusted to allow for columns and then add Customer reference as a criteria in the SUMIFS

Thanks
Rob H
Rob Henson

Having now seen the data, it is in columns so will need tweaking.
Rob Henson

You can do this with a pivot table.

Create a pivot on the data using customer as Row and Date as column. Select one of the dates and right click and select group. Choose Month and Year and OK. Then you can rearrange the columns in field picker options on the right hand side, drag the Years box below Inv Date and that will show as you need it.

Just thought, that will show all of a month so you might not be comparing like with like; ie all of Feb 2015 with Feb 2016 to date.
SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Lawrence Salvucci

@ ProfessorJimJam - I uploaded the sample file.

@ Rob Henson - I will review your formula and see how that works.
Lawrence Salvucci

@ Rob - That works for January since the month is over but how would I do the current month so it sums each year through the current Month & Day?

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Lawrence Salvucci