Link to home
Create AccountLog in
Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America

asked on

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?
Avatar of Professor J
Professor J

can you upload a dummy example file so that i build formula in it?
Avatar of 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
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
Having now seen the data, it is in columns so will need tweaking.
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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@ ProfessorJimJam - I uploaded the sample file.

@ Rob Henson - I will review your formula and see how that works.
@ 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?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you very much! it works like a charm and its exactly what I needed! Thank you again!!
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.

=SUMIFS(Tbl_Cust[Invoiced Amount], Tbl_Cust[Customer Name], $A8, Tbl_Cust[Inv Date], ">=" & E$3, Tbl_Cust[Inv Date], "<=" & E$4)
EE-28927549.xlsx
I did notice that an added it myself but thank you for sending a new file with the additions.