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 Last year (2019) YTD and QTD for current year(oct -dec 2020). Below is my data. I was able to calculate Current year YTD (2020) using calculation =SUMPRODUCT((YEAR(Sheet1!$B$1)<=YEAR(Data!$B$2:$B$40))*(Data!$A$2:$A$40='Total Batches'!$B$1)*(Data!$G$2:$G$40)). Now I am stuck with the calculation for prior year and QTD.
Appreciate your help
TIA
I am seeking for help in one of the calculation where I need to calculate Last year (2019) YTD and QTD for current year(oct -dec 2020). Below is my data. I was able to calculate Current year YTD (2020) using calculation =SUMPRODUCT((YEAR(Sheet1!$B$1)<=YEAR(Data!$B$2:$B$40))*(Data!$A$2:$A$40='Total Batches'!$B$1)*(Data!$G$2:$G$40)). Now I am stuck with the calculation for prior year and QTD.
Site | Date | Month | Year | Total Batches | Total Rejection |
Texas | 12/1/19 | 12 | 2019 | 4 | 0 |
Boston | 12/1/19 | 12 | 2019 | 5 | 4 |
Chicago | 12/1/19 | 12 | 2019 | 6 | 0 |
New Jersey | 12/1/19 | 12 | 2019 | 1 | 4 |
Chicago | 1/1/20 | 1 | 2020 | 90 | 7 |
New Jersey | 1/1/20 | 1 | 2020 | 6 | 5 |
New York | 1/1/20 | 1 | 2020 | 0 | 3 |
Philadelphia | 2/1/20 | 2 | 2020 | 10 | 0 |
Maryland | 2/1/20 | 2 | 2020 | 15 | 2 |
Delawar | 3/1/20 | 3 | 2020 | 30 | 0 |
Texas | 3/1/20 | 3 | 2020 | 4 | 0 |
Boston | 4/1/20 | 4 | 2020 | 3 | 0 |
Chicago | 4/1/20 | 4 | 2020 | 7 | 2 |
New Jersey | 5/1/20 | 5 | 2020 | 8 | 3 |
New York | 5/1/20 | 5 | 2020 | 30 | 1 |
Philadelphia | 6/1/20 | 6 | 2020 | 15 | 5 |
Ohio | 6/1/20 | 6 | 2020 | 2 | 0 |
Maryland | 6/1/20 | 6 | 2020 | 7 | 2 |
Delawar | 7/1/20 | 7 | 2020 | 5 | 0 |
Texas | 7/1/20 | 7 | 2020 | 4 | 0 |
Boston | 8/1/20 | 8 | 2020 | 3 | 0 |
Chicago | 8/1/20 | 8 | 2020 | 7 | 2 |
New Jersey | 9/1/20 | 9 | 2020 | 8 | 3 |
New York | 9/1/20 | 9 | 2020 | 2 | 1 |
Philadelphia | 9/1/20 | 9 | 2020 | 5 | 5 |
Ohio | 10/1/20 | 10 | 2020 | 10 | 0 |
Maryland | 10/1/20 | 10 | 2020 | 15 | 2 |
Delawar | 11/1/20 | 11 | 2020 | 5 | 0 |
Texas | 11/1/20 | 11 | 2020 | 4 | 0 |
Boston | 12/1/20 | 12 | 2020 | 3 | 0 |
Chicago | 12/1/20 | 12 | 2020 | 7 | 2 |
New Jersey | 12/1/20 | 12 | 2020 | 8 | 3 |
New York | 12/1/20 | 12 | 2020 | 3 | 1 |
Philadelphia | 12/1/20 | 12 | 2020 | 1 | 5 |
Appreciate your help
TIA