Hi all,
I am currently looking for a more complex "countifs" formula.
I would like to identify in each row if the combination IDa_IDb can be found in the past five years. As an example (please see my table below):
- In row 1, we find the combination: IDa = 20, IDb = 3, in 2016. As we do not know anything about previous years, the count should be 0.
- In row 3, we find the combination: IDa = 20, IDb = 3, again. As we also find it in 2016 (row 1), the count should be 1.
Unfortunately, I have a dataset of more than 10 million rows and I do not know how to create such "count" column. Does anyone have an idea how to solve it? I would really appreciate it!
Thanks a lot!
Best,
Alexander
| ID | IDa | Year | IDb | Count |
| 1 | 20 | 2016 | 3 | 0 |
| 1 | 20 | 2016 | 4 | 0 |
| 2 | 20 | 2017 | 3 | 1 |
| 3 | 20 | 2018 | 3 | 1 |
| 3 | 20 | 2018 | 4 | 1 |
| 3 | 20 | 2018 | 5 | 0 |
| 4 | 20 | 2019 | 5 | 1 |
| 5 | 22 | 2016 | 3 | 0 |
| 5 | 22 | 2016 | 4 | 0 |
