Current year vs. prior year

P

Pearl_022

New Member
Joined
Jan 21, 2021
Messages
22
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
  2. MacOS
Hi Experts,

I am seeking for help in one of the calculation where I need to calculate current year YTD vs. Prior year with out creating pivot table. Below is my data. I was able to calculate Current year YTD using calculation =SUMPRODUCT((YEAR('Reporting Month '!B1)<=YEAR(Data!E2:E193))*(Data!A2:A193=Pivot!B1)*(Data!F2:F193)). Now I am stuck with the calculation for vs. prior year.

For example if I am getting current year YTD = 11 and my prior Year value is 10 for New Jersey site then I want to show 1 as a result.

SiteYearsDate OccurredMonth No.Date ConcatReportsInjuriesDamage
NewJersey
2020​
Jan
1​
1/2020
1​
1​
0​
NewJersey
2020​
Feb
2​
2/2020
0​
0​
0​
NewJersey
2020​
Mar
3​
3/2020
0​
0​
0​
NewJersey
2020​
Apr
4​
4/2020
0​
0​
0​
NewJersey
2020​
May
5​
5/2020
3​
1​
0​
NewJersey
2020​
Jun
6​
6/2020
2​
0​
0​
NewJersey
2020​
Jul
7​
7/2020
0​
0​
0​
NewJersey
2020​
Aug
8​
8/2020
0​
0​
0​
NewJersey
2020​
Sep
9​
9/2020
1​
0​
0​
NewJersey
2020​
Oct
10​
10/2020
2​
1​
0​
NewJersey
2020​
Nov
11​
11/2020
3​
0​
0​
NewJersey
2020​
Dec
12​
12/2020
1​
0​
0​
NewJersey
2021​
Jan
1​
1/2021
1​
0​
0​
NewJersey
2021​
Feb
2​
2/2021
0​
0​
0​
NewJersey
2021​
Mar
3​
3/2021
0​
0​
0​
NewJersey
2021​
Apr
4​
4/2021
0​
0​
0​
NewJersey
2021​
May
5​
5/2021
0​
0​
0​
NewJersey
2021​
Jun
6​
6/2021
0​
0​
0​
NewJersey
2021​
Jul
7​
7/2021
0​
0​
0​
NewJersey
2021​
Aug
8​
8/2021
0​
0​
0​
NewJersey
2021​
Sep
9​
9/2021
0​
0​
0​
NewJersey
2021​
Oct
10​
10/2021
0​
0​
0​
NewJersey
2021​
Nov
11​
11/2021
0​
0​
0​
NewJersey
2021​
Dec
12​
12/2021
0​
0​
0​
New York
2020​
Jan
1​
1/2020
0​
0​
0​
New York
2020​
Feb
2​
2/2020
1​
1​
0​
New York
2020​
Mar
3​
3/2020
2​
2​
0​
New York
2020​
Apr
4​
4/2020
0​
0​
0​
New York
2020​
May
5​
5/2020
0​
0​
0​
New York
2020​
Jun
6​
6/2020
5​
4​
0​
New York
2020​
Jul
7​
7/2020
0​
0​
0​
New York
2020​
Aug
8​
8/2020
0​
0​
0​
New York
2020​
Sep
9​
9/2020
2​
2​
0​
New York
2020​
Oct
10​
10/2020
0​
0​
0​
New York
2020​
Nov
11​
11/2020
7​
1​
0​
New York
2020​
Dec
12​
12/2020
4​
2​
0​
New York
2021​
Jan
1​
1/2021
9​
1​
0​
New York
2021​
Feb
2​
2/2021
0​
0​
0​
New York
2021​
Mar
3​
3/2021
0​
0​
0​
New York
2021​
Apr
4​
4/2021
0​
0​
0​
New York
2021​
May
5​
5/2021
0​
0​
0​
New York
2021​
Jun
6​
6/2021
0​
0​
0​
New York
2021​
Jul
7​
7/2021
0​
0​
0​
New York
2021​
Aug
8​
8/2021
0​
0​
0​
New York
2021​
Sep
9​
9/2021
0​
0​
0​
New York
2021​
Oct
10​
10/2021
0​
0​
0​
New York
2021​
Nov
11​
11/2021
0​
0​
0​
New York
2021​
Dec
12​
12/2021
0​
0​
0​
Denver
2020​
Jan
1​
1/2020
0​
0​
0​
Denver
2020​
Feb
2​
2/2020
0​
0​
0​
Denver
2020​
Mar
3​
3/2020
0​
0​
0​
Denver
2020​
Apr
4​
4/2020
0​
0​
0​
Denver
2020​
May
5​
5/2020
0​
0​
0​
Denver
2020​
Jun
6​
6/2020
0​
0​
0​
Denver
2020​
Jul
7​
7/2020
1​
0​
0​
Denver
2020​
Aug
8​
8/2020
2​
0​
0​
Denver
2020​
Sep
9​
9/2020
0​
0​
0​
Denver
2020​
Oct
10​
10/2020
0​
0​
0​
Denver
2020​
Nov
11​
11/2020
1​
0​
0​
Denver
2020​
Dec
12​
12/2020
0​
0​
0​
Denver
2021​
Jan
1​
1/2021
1​
0​
0​
Denver
2021​
Feb
2​
2/2021
0​
0​
0​
Denver
2021​
Mar
3​
3/2021
0​
0​
0​
Denver
2021​
Apr
4​
4/2021
0​
0​
0​
Denver
2021​
May
5​
5/2021
0​
0​
0​
Denver
2021​
Jun
6​
6/2021
0​
0​
0​
Denver
2021​
Jul
7​
7/2021
0​
0​
0​
Denver
2021​
Aug
8​
8/2021
0​
0​
0​
Denver
2021​
Sep
9​
9/2021
0​
0​
0​
Denver
2021​
Oct
10​
10/2021
0​
0​
0​
Denver
2021​
Nov
11​
11/2021
0​
0​
0​
Denver
2021​
Dec
12​
12/2021
0​
0​
0​

Appreciate your help.

TIA
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
I'm not sure I understand, but:
MrExcelPlayground.xlsm
ABCDEFGHIJKLM
1SiteYearsDate OccurredMonth No.Date ConcatReportsInjuriesDamageCur YTD - Last Year
2NewJersey2020Jan1Jan-20110LocationReportsInjuriesDamage
3NewJersey2020Feb2Feb-20000NewJersey-12-30
4NewJersey2020Mar3Mar-20000New York-12-110
5NewJersey2020Apr4Apr-20000Denver-300
6NewJersey2020May5May-20310
7NewJersey2020Jun6Jun-20200
8NewJersey2020Jul7Jul-20000
9NewJersey2020Aug8Aug-20000
10NewJersey2020Sep9Sep-20100
11NewJersey2020Oct10Oct-20210
12NewJersey2020Nov11Nov-20300
13NewJersey2020Dec12Dec-20100
14NewJersey2021Jan1Jan-21100
15NewJersey2021Feb2Feb-21000
16NewJersey2021Mar3Mar-21000
17NewJersey2021Apr4Apr-21000
18NewJersey2021May5May-21000
19NewJersey2021Jun6Jun-21000
20NewJersey2021Jul7Jul-21000
21NewJersey2021Aug8Aug-21000
22NewJersey2021Sep9Sep-21000
23NewJersey2021Oct10Oct-21000
24NewJersey2021Nov11Nov-21000
25NewJersey2021Dec12Dec-21000
26New York2020Jan1Jan-20000
27New York2020Feb2Feb-20110
28New York2020Mar3Mar-20220
29New York2020Apr4Apr-20000
30New York2020May5May-20000
31New York2020Jun6Jun-20540
32New York2020Jul7Jul-20000
33New York2020Aug8Aug-20000
34New York2020Sep9Sep-20220
35New York2020Oct10Oct-20000
36New York2020Nov11Nov-20710
37New York2020Dec12Dec-20420
38New York2021Jan1Jan-21910
39New York2021Feb2Feb-21000
40New York2021Mar3Mar-21000
41New York2021Apr4Apr-21000
42New York2021May5May-21000
43New York2021Jun6Jun-21000
44New York2021Jul7Jul-21000
45New York2021Aug8Aug-21000
46New York2021Sep9Sep-21000
47New York2021Oct10Oct-21000
48New York2021Nov11Nov-21000
49New York2021Dec12Dec-21000
50Denver2020Jan1Jan-20000
51Denver2020Feb2Feb-20000
52Denver2020Mar3Mar-20000
53Denver2020Apr4Apr-20000
54Denver2020May5May-20000
55Denver2020Jun6Jun-20000
56Denver2020Jul7Jul-20100
57Denver2020Aug8Aug-20200
58Denver2020Sep9Sep-20000
59Denver2020Oct10Oct-20000
60Denver2020Nov11Nov-20100
61Denver2020Dec12Dec-20000
62Denver2021Jan1Jan-21100
63Denver2021Feb2Feb-21000
64Denver2021Mar3Mar-21000
65Denver2021Apr4Apr-21000
66Denver2021May5May-21000
67Denver2021Jun6Jun-21000
68Denver2021Jul7Jul-21000
69Denver2021Aug8Aug-21000
70Denver2021Sep9Sep-21000
71Denver2021Oct10Oct-21000
72Denver2021Nov11Nov-21000
73Denver2021Dec12Dec-21000
Sheet31
Cell Formulas
RangeFormula
K3:K5K3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$F$2:$F$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$F$2:$F$73)
L3:L5L3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$G$2:$G$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$G$2:$G$73)
M3:M5M3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$H$2:$H$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$H$2:$H$73)
 
Upvote 0
JamesCanale said:
I'm not sure I understand, but:
MrExcelPlayground.xlsm
ABCDEFGHIJKLM
1SiteYearsDate OccurredMonth No.Date ConcatReportsInjuriesDamageCur YTD - Last Year
2NewJersey2020Jan1Jan-20110LocationReportsInjuriesDamage
3NewJersey2020Feb2Feb-20000NewJersey-12-30
4NewJersey2020Mar3Mar-20000New York-12-110
5NewJersey2020Apr4Apr-20000Denver-300
6NewJersey2020May5May-20310
7NewJersey2020Jun6Jun-20200
8NewJersey2020Jul7Jul-20000
9NewJersey2020Aug8Aug-20000
10NewJersey2020Sep9Sep-20100
11NewJersey2020Oct10Oct-20210
12NewJersey2020Nov11Nov-20300
13NewJersey2020Dec12Dec-20100
14NewJersey2021Jan1Jan-21100
15NewJersey2021Feb2Feb-21000
16NewJersey2021Mar3Mar-21000
17NewJersey2021Apr4Apr-21000
18NewJersey2021May5May-21000
19NewJersey2021Jun6Jun-21000
20NewJersey2021Jul7Jul-21000
21NewJersey2021Aug8Aug-21000
22NewJersey2021Sep9Sep-21000
23NewJersey2021Oct10Oct-21000
24NewJersey2021Nov11Nov-21000
25NewJersey2021Dec12Dec-21000
26New York2020Jan1Jan-20000
27New York2020Feb2Feb-20110
28New York2020Mar3Mar-20220
29New York2020Apr4Apr-20000
30New York2020May5May-20000
31New York2020Jun6Jun-20540
32New York2020Jul7Jul-20000
33New York2020Aug8Aug-20000
34New York2020Sep9Sep-20220
35New York2020Oct10Oct-20000
36New York2020Nov11Nov-20710
37New York2020Dec12Dec-20420
38New York2021Jan1Jan-21910
39New York2021Feb2Feb-21000
40New York2021Mar3Mar-21000
41New York2021Apr4Apr-21000
42New York2021May5May-21000
43New York2021Jun6Jun-21000
44New York2021Jul7Jul-21000
45New York2021Aug8Aug-21000
46New York2021Sep9Sep-21000
47New York2021Oct10Oct-21000
48New York2021Nov11Nov-21000
49New York2021Dec12Dec-21000
50Denver2020Jan1Jan-20000
51Denver2020Feb2Feb-20000
52Denver2020Mar3Mar-20000
53Denver2020Apr4Apr-20000
54Denver2020May5May-20000
55Denver2020Jun6Jun-20000
56Denver2020Jul7Jul-20100
57Denver2020Aug8Aug-20200
58Denver2020Sep9Sep-20000
59Denver2020Oct10Oct-20000
60Denver2020Nov11Nov-20100
61Denver2020Dec12Dec-20000
62Denver2021Jan1Jan-21100
63Denver2021Feb2Feb-21000
64Denver2021Mar3Mar-21000
65Denver2021Apr4Apr-21000
66Denver2021May5May-21000
67Denver2021Jun6Jun-21000
68Denver2021Jul7Jul-21000
69Denver2021Aug8Aug-21000
70Denver2021Sep9Sep-21000
71Denver2021Oct10Oct-21000
72Denver2021Nov11Nov-21000
73Denver2021Dec12Dec-21000
Sheet31
Cell Formulas
RangeFormula
K3:K5K3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$F$2:$F$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$F$2:$F$73)
L3:L5L3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$G$2:$G$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$G$2:$G$73)
M3:M5M3=SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())),$H$2:$H$73)-SUMPRODUCT(--($A$2:$A$73=J3),--($B$2:$B$73=YEAR(TODAY())-1),$H$2:$H$73)
Click to expand...
Hi James,

Thanks for the response. Seems like your calculation is correct but when I try to make use with my filters its not working and giving me result as 0. Attaching the formate which I need to use this might help. I am not great at excel so please excuse if I am asking you silly questions.
 

Attachments

  • Screen Shot 2021-02-26 at 10.46.43 AM.png
    Screen Shot 2021-02-26 at 10.46.43 AM.png
    218.6 KB · Views: 5
Upvote 0
Pearl_022 said:
Hi James,

Thanks for the response. Seems like your calculation is correct but when I try to make use with my filters its not working and giving me result as 0. Attaching the formate which I need to use this might help. I am not great at excel so please excuse if I am asking you silly questions.
Click to expand...

Pearl_022 said:
Hi James,

Thanks for the response. Seems like your calculation is correct but when I try to make use with my filters its not working and giving me result as 0. Attaching the formate which I need to use this might help. I am not great at excel so please excuse if I am asking you silly questions.
Click to expand...
Thank you James. My calculation is working fine. Thanks again for all your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
131
Peter_SSs
Peter_SSs
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
81
RoryA
RoryA
D
  • Solved
Returning the unique label whether a date falls within a list of date ranges that have all been uniquely labeled.
Replies
2
Views
354
drmurph
D
E
  • Question
average or sumproduct?
Replies
0
Views
282
excelnewbie26
E
N
  • Solved
Pivot Table Not Working
Replies
3
Views
146
neasds
N

Forum statistics

Threads
1,214,523
Messages
6,120,031
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top