google-sheetslambda

Lambda is not iterated in an Array

I'm stuck in a situation like the one you can see here:
11/04/2017 $1,000.00 2017 1000
01/01/2018 $10,002.00 2018 0
01/07/2018 $10,002.00 2019 0
01/01/2019 $1,000.00 2020 0
01/06/2019 $100.00 2021 0
01/01/2020 $1,000.00 2022 0
01/01/2021 $10,008.00 2023 0
01/01/2022 $1,000.00 2024 0
12/09/2022 $1,000.00
01/01/2023 $800.00
01/01/2024 $1,000.00

What I'm trying to achieve is selecting all unique ranges of years in B, and for them making a sum of C values in F.

As it stands the filter in uniques works well, but something is not working with the sum, and I don't get what I'm doing wrong, the table in E:F is created with this formula:

=MAP(UNIQUE(INDEX(RIGHT(B5:B15,4))), UNIQUE(INDEX(RIGHT(B5:B15,2))) , lambda(a, b, {a,  SUMIF(RIGHT(B5:B15,4),a,C5:C15)}))

Is maybe a in the lambda not iterable?

Thank you for your help

Solution

  • You may try:

    =map(unique(sort(year(B5:B15))),lambda(Σ,{Σ,sum(ifna(filter(C:C,year(B:B)=Σ)))}))

    enter image description here

    you can also replace the part

    sum(ifna(filter(C:C,year(B:B)=Σ)))

    with

    sumif(index(year(B:B)),Σ,C:C)