Countifs in a table to return payroll of that year

[aggie81]

I download payroll data then using Get Data transform the raw data into a table with multiple years, 2015 thru 2021.
I want to create a column that will tell me what the payroll number it is for that year, 1 thru 26

January 16, 2015 would be payroll 1 for 2015
January 30, 2015 would be payroll 2 for 2015
January 15, 2016 would be payroll 1 for 2016
January 29, 2016 would be payroll 2 for 2016

I have tried using countifs() with the check date and year column but it doesn't work.
There are 40,000+ rows.

Thanks for reading and hope someone can help.
Lee

 

[pecoflyer]

A sample file with some data and some expected results would be mots helpful.
Due to the sanitary crisis, crystal balls are in short supply.
Thanks

 

[aggie81]

First it was toilet paper and now crystal balls, what is next?

Thanks for replying.

In the Pay Period column something like this.
As long as the Check Yr is the same, place a starting number of 1 in the Pay Period for each matching Check Date and increase that number by one for each change in Check Date until the Check Yr changes and then start over at 1 for each year.

Thanks,
Lee

 

[vletm]

aggie81
Could You solve that without any file?
Here one possible sample ...
I-column is as a help column.
Cells G2 has formulas.
You could use it as You would like to do.

 

[aggie81]

Thank you vletm for the solution. It works well. I don't understand it but it works.
If you have time can you tell me why it works?
Could it be done using Unique() to not use the helper pivot table?
Thank you.

 

[vletm]

aggie81
Which part do You understand?
Why it works? ... it'll do as You've written.
It's is one sample - how to solve Your challenge.
If You've thought ... there could be some unwanted results ... but it works as You've written.
... using Unique() ... sure ... I can write that term somewhere,
but I won't use it - because it won't work with my Excel - if I cannot test/verify then useless function.
You could test this to cell G1 =IF(B1<>B2,1,IF(F1= F2,G1,G1+1)). It won't need Pivot-table.
Actually,
I would solve that challenge different way
- if I would need to use something like that.
But then I should know - what do You really need?