QTD calculation 2019 YTD calculation

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

SiteDateMonthYearTotal BatchesTotal 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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Why don't you just PivotTable the lot, and then group the date by year and quarter:
 

Attachments

  • PivotPic.JPG
    PivotPic.JPG
    59.5 KB · Views: 3
Upvote 0
I know i can do that but this is not what my client wants they want to use calculation instead of pivot table.
 
Upvote 0
Well, first calculate the tests you want to apply ... like calculate prior year number, and also calculate the quarter start date, and the quarter end date, and use those in similar SUMPRODUCT formulas.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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