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
ASKER
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
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.
Thank you very much! it works like a charm and its exactly what I needed! Thank you again!!
Jerry Paladino
Lawrence,
In the previous file I did not link the SUMIFS to the Customer Name in Column A. The attached file now does that with the SUMIFS formulas changed to this syntax.