YTD Total Compares to Same Period Automatically Based on Current Month

[jayfeitlinger]

I have an Excel spreadsheet that has each month reporting website visits. I also have next to each month last year website visits. I want to have a YTD column to see how the same months last year and this year are in comparison. What formula can I use that compares the same months from last year to this year?

For more details column A is January 2015 and Column B is January 2016 and then AD is 2015 YTD and Column AE is 2016 YTD. When I open up the spreadsheet I want Column AD and AE to automatically add up the right numbers. For example, if I open the sheet today on October 1st the YTD column would only add up January through September. Any help?

 

[jorismoerings]

Maybe something like this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	O	P	Q	R	S	T	U	V	W	X	AD	AE
1	2015-01	2016-01	2015-02	2016-02	2015-08	2016-08	2015-09	2016-09	2015-10	2016-10	2015-11	2016-11	2015-12	2016-12	YTD 2015	YTD 2016
2	10	100	10	100	10	100	10	100	10	100	10	100	10	100	90	900
3															90	900
Sheet1

Cell Formulas
Range	Formula
AD2	=SUMPRODUCT((YEAR(OFFSET($A$1,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($A$1,1,0,1,(MONTH(TODAY())*2)-2))
AD3	=SUMPRODUCT(NOT(ISEVEN(COLUMN(OFFSET($A$1,0,0,1,(MONTH(TODAY())*2)-2))))*OFFSET($A$1,1,0,1,(MONTH(TODAY())*2)-2))
AE2	=SUMPRODUCT((YEAR(OFFSET($A$1,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($A$1,1,0,1,(MONTH(TODAY())*2)-2))
AE3	=SUMPRODUCT((ISEVEN(COLUMN(OFFSET($A$1,0,0,1,(MONTH(TODAY())*2)-2))))*OFFSET($A$1,1,0,1,(MONTH(TODAY())*2)-2))

 

[jorismoerings]

The formula given are for 2 possible options:
1. formula in AD2 en AE2 if you are using a header row with dates
2. formula in AD3 en AE3 if you are NOT using a header row with dates

 

[jayfeitlinger]

Thanks so much! That seemed to work great for the 1st row. I have data in the 1st column to identify each row and another header row so I adjusted the $A$1 to match the 1st date of $B$2. The challenge when I copy down to all the other rows in column Z and AA it is adding up only that row 3. What am I doing wrong to make this work for all rows? Below picture shows screenshot with Z6 formula showing. My assumption is that I need to add another change to the formula so it adds up the months in that specific row, not row 3 each time.

I also noticed that in your example you had the YTD total in columns AD and AE. I am not sure why I am 3 columns off from yours and mine actually had an extra column in Column A?

 

[jorismoerings]

Hi,

Not sure what went wrong beside the relative and absolute cell references i put in my formula.
Try changing your formula's to trhis:

Excel 2016 (Windows) 64 bit
	A	B	C	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2		2015-01	2016-01	2015-08	2016-08	2015-09	2016-09	2015-10	2016-10	2015-11	2016-11	2015-12	2016-12	YTD 15	YTD 16
3	Row 1	10	100	10	100	10	100	10	100	10	100	10	100	90	900
4	Row 2	11	110	11	110	11	110	11	110	11	110	11	110	99	990
5	Row 3	12	120	12	120	12	120	12	120	12	120	12	120	108	1080
6	Row 4	13	130	13	130	13	130	13	130	13	130	13	130	117	1170
7	Row 5	14	140	14	140	14	140	14	140	14	140	14	140	126	1260
8	Row 6	15	150	15	150	15	150	15	150	15	150	15	150	135	1350
Sheet1

Cell Formulas
Range	Formula
Z3	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B3,0,0,1,(MONTH(TODAY())*2)-2))
Z4	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B4,0,0,1,(MONTH(TODAY())*2)-2))
Z5	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B5,0,0,1,(MONTH(TODAY())*2)-2))
Z6	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B6,0,0,1,(MONTH(TODAY())*2)-2))
Z7	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B7,0,0,1,(MONTH(TODAY())*2)-2))
Z8	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B8,0,0,1,(MONTH(TODAY())*2)-2))
AA3	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B3,0,0,1,(MONTH(TODAY())*2)-2))
AA4	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B4,0,0,1,(MONTH(TODAY())*2)-2))
AA5	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B5,0,0,1,(MONTH(TODAY())*2)-2))
AA6	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B6,0,0,1,(MONTH(TODAY())*2)-2))
AA7	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B7,0,0,1,(MONTH(TODAY())*2)-2))
AA8	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B8,0,0,1,(MONTH(TODAY())*2)-2))

HtH

 

[jayfeitlinger]

That worked perfectly. Beyond appreciate it! I spent hours trying to get that to work.

Curious, if I wanted to add 1 column after the YTD to show current month results, any suggestions on what formula I can use? For example, I want to create a dashboard tab in the Excel worksheet. I was able to use the YTD columns to build that out to show YTD comparison; however, we also want to show current month results. I can easily on the dashboard tab = to that month on the data tab; however, next month I would have to manually move each of those dashboard cells. What I would love to do is just add 1 more column at end after the YTD. In this formula I would want it to determine automatically what month it currently is and use that column info.

I tried using VLOOKUP but that did not work

=VLOOKUP(AC1,$A$1:$M$3,MATCH(TEXT(TODAY(),"MMM"),$A$1:$M$1,0),FALSE)

 

[jorismoerings]

you could pretty much use the same formula with a few alterations:

Create 2 helper columns in columns AB and AC.
AB3 is the onthly result from 2015 and will be something like this:
=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B3,0,(MONTH(TODAY())*2)-2))
AC3 is the onthly result from 2016 and will be something like this:
=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B3,0,(MONTH(TODAY())*2)-2))

Like this you can easily relate your dashboard to the same cell every time. If you dont want the helper columns you need to change the cell references and add the TAb-name of the correct sheet.


HtH

 

[jayfeitlinger]

First, @jorismoerings thank you for all this help!

Unfortunately that did not work. What I am trying to do is have that final column figure out what the specific month it currently is and put exactly that number in the cell. For example, the YTD formula you provided before when I open up the document in October, my assumption is it would add up January through September for that year. The cell next to that under YTD 2016 would be one cell over under each month for current year to date. If I open the document on 11/1 it would then update those YTD to include Jan through October, correct?

On the dashboard, I want to show current month to date results. I have the dashboard manually equalling the other data tab and its working fine. However, next month I need to then go onto the dashboard and manually change where its pulling from. My preference would be on the dashboard to link it to a new column at end that equals the current month. So that way its always using the current month.

With the formula you provided it appears to be using more than current month. How do I set up that formula so it only pulls from the current month and puts that number in its cell?

 

[jorismoerings]

Hi, your right. Answrede to quickly without thinking it through.

This should do it:

Excel 2016 (Windows) 64 bit
	Z	AA	AB	AC
2	YTD 2015	YTD2016	MTD 2015	MTD 2016
3	544050	598752	108810	114912
4	725400	798336	145080	153216
5	906750	997920	181350	191520
6	1088100	1197504	217620	229824
7	1269450	1397088	253890	268128
8	1450800	1596672	290160	306432
9	1632150	1796256	326430	344736
Sheet1

Cell Formulas
Range	Formula
Z3	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())-1))*OFFSET($B3,0,0,1,(MONTH(TODAY())*2)-2))
AA3	=SUMPRODUCT((YEAR(OFFSET($B$2,0,0,1,(MONTH(TODAY())*2)-2))=(YEAR(TODAY())))*OFFSET($B3,0,0,1,(MONTH(TODAY())*2)-2))
AB3	=OFFSET($A3,0,(MONTH(TODAY())*2)-3)
AC3	=OFFSET($A3,0,(MONTH(TODAY())*2)-2)