Pearl_022
New Member
- Joined
- Jan 21, 2021
- Messages
- 22
- Office Version
- 365
- 2016
- 2013
- 2010
- Platform
- Windows
- 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.
Appreciate your help.
TIA
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.
Site | Years | Date Occurred | Month No. | Date Concat | Reports | Injuries | Damage |
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