LAMBDA Assistance

[SaraW]

Hi experts!
I'm trying to teach myself the LAMBDA function and am struggling.
I found an awesome walk through here:

Sum by quarter

To sum values by quarter, you can use a formula based on the SUMIFS function along with a helper column that contains quarters. In the example shown, the formula in G5 is: =SUMIFS(data[Amount],data[Qtr],F5) where data is an Excel Table in the range B5:D16, and the quarters in column E are...

exceljet.net

but am struggling to understand how to adjust to show
Year | Quarter | Total

Any assistance would be appreciated

 

[Debaser]

Here's one way:

=LET(
dates,data[Date],
amounts,data[Amount],
years,YEAR(dates),
uyears,UNIQUE(years),
quarters,ROUNDUP(MONTH(dates)/3,0),
uquarters,UNIQUE(quarters),
YrQtr,IFERROR(UNIQUE(FILTER(DATE(YEAR(data[Date]), ROUNDUP(MONTH(data[Date])/3,0), 1), data[Date]<>"")), ""),
totals,BYROW(YrQtr,LAMBDA(r,SUM((years=YEAR(r))*(quarters=MONTH(r))*amounts))),
result,VSTACK({"YrQtr","Qtr","Total"},HSTACK(YEAR(YrQtr),MONTH(YrQtr),totals)),
result)

Note: If you name every part, including the final result, in LET, it's much easier to change the last argument to output any step you need to verify.

 

[SaraW]

Thank you!
I shall keep trying to pull these things apart until I completely understand them

 

[Peter Bartholomew]

You may not have them yet, but there are a couple of new functions around (GROUPBY and PIVOTBY).
A single formula will generate the table

=GROUPBY(
    HSTACK(data[[#All],[Year]], data[[#All],[Quarter]]),
    data[[#All],[Amount]],
    SUM,
    3,
    2
)

or, rather more pared back

=GROUPBY(
    HSTACK(data[Year], data[Quarter]),
    data[Amount],
    SUM,
    ,
    0
)

 

[p45cal]

Without helper columns:

=GROUPBY(YEAR(data[Date]) & " Qtr" & ROUNDUP(MONTH(data[Date])/3,0),data[Amount],SUM)

with headers:

=GROUPBY(IFERROR(YEAR(data[[#All],[Date]]) & " Qtr" & ROUNDUP(MONTH(data[[#All],[Date]])/3,0),"Year/Qtr"),data[[#All],[Amount]],SUM,3)

 

[SaraW]

Wow - I have a lot to wrap my head around.
Thank you