I looked through the related questions, but none seemed to match this setup. Given a personnel table with begin and end dates, I would like to sum by year to allocation of each person. Table example:

           | begin    | end        | 2021 | 2022 | 2023 -----------+----------+------------+------+------+------ PersonA    | 1/1/2021 | 12/31/2022 |      |      | PersonB    | 7/1/2021 |  6/30/2023 |      |      | -----------+----------+------------+------+------+------ allocation |          |            | a_21 | a_22 | a_23 

a_21 should amount to 1.0 + 0.5 = 1.5, a_22 to 2.0, a_23 to 0.5.

The trivial option would be calculating YEARFRAC per year in each cell, but in my spreadsheet I have other types of values in the cell. Is there a way to calculate the sums a_21, a_22, a_23 without creating the intermediate values?

I've tried a SUM(YEARFRAC(MAX(B,DATE(D1,1,1)),MIN(C,DATE(D1,12,31)))), but YEARFRAC is evaluated on the ranges B and C but not individually by row first.