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)=Σ)))}))
you can also replace the part
sum(ifna(filter(C:C,year(B:B)=Σ)))
with
sumif(index(year(B:B)),Σ,C:C)