Avatar of Lawrence Salvucci
Lawrence Salvucci
Flag 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?
Microsoft Excel

Avatar of undefined
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

Thanks
Rob H
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Lawrence Salvucci

ASKER
@ ProfessorJimJam - I uploaded the sample file.

@ Rob Henson - I will review your formula and see how that works.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lawrence Salvucci

ASKER
@ 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
Jerry Paladino

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lawrence Salvucci

ASKER
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.

=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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lawrence Salvucci

ASKER
I did notice that an added it myself but thank you for sending a new file with the additions.