Spill range help

[razaas]

Hi, with the below formula I get a spilled range in 2 columns and 3 rows, I want to get total of the 3 values in 4th row, as example below. Thanks

Col A Col B

AAA 123

BBB 234

CCC 345

Total 702

=LET(pm,SORT(UNIQUE(Table3[Payment Mode]),1,1),

amt,SUMIFS(Table3[Amount],Table3[Payment Mode],pm),

CHOOSE({1,2},pm,amt))

 

[djc]

With Excel 2021, you could try the following:

=LET(
    pm, SORT(UNIQUE(Table3[Payment Mode])),
    amt, SUMIFS(Table3[Amount], Table3[Payment Mode], pm),
    body, CHOOSE({1,2}, pm, amt),
    foot, CHOOSE({1,2}, "Total", SUM(amt)),
    IFERROR(INDEX(body, SEQUENCE(ROWS(body) + 1), {1,2}), foot)
)

Or if you also wanted to incorporate the headers:

=LET(
    pm, SORT(UNIQUE(Table3[Payment Mode])),
    amt, SUMIFS(Table3[Amount], Table3[Payment Mode], pm),
    head, Table3[#Headers],
    body, CHOOSE({1,2}, pm, amt),
    foot, CHOOSE({1,2}, "Total", SUM(amt)),
    row_nums, SEQUENCE(ROWS(body) + 2, , 0),
    IFERROR(IF(row_nums, INDEX(body, row_nums, {1,2}), head), foot)
)

 

[razaas]

With Excel 2021, you could try the following:

=LET(
    pm, SORT(UNIQUE(Table3[Payment Mode])),
    amt, SUMIFS(Table3[Amount], Table3[Payment Mode], pm),
    body, CHOOSE({1,2}, pm, amt),
    foot, CHOOSE({1,2}, "Total", SUM(amt)),
    IFERROR(INDEX(body, SEQUENCE(ROWS(body) + 1), {1,2}), foot)
)

Or if you also wanted to incorporate the headers:

=LET(
    pm, SORT(UNIQUE(Table3[Payment Mode])),
    amt, SUMIFS(Table3[Amount], Table3[Payment Mode], pm),
    head, Table3[#Headers],
    body, CHOOSE({1,2}, pm, amt),
    foot, CHOOSE({1,2}, "Total", SUM(amt)),
    row_nums, SEQUENCE(ROWS(body) + 2, , 0),
    IFERROR(IF(row_nums, INDEX(body, row_nums, {1,2}), head), foot)
)

Thanks a lot, without header and with header both works, great help, highly appreciate. Thanks

 

[djc]

Thanks a lot, without header and with header both works, great help, highly appreciate. Thanks

I probably should have defined the head variable with a static array {"Payment Mode","Amount"} instead of using Table3[#Headers]. My simple mock-up table only contained those two columns, and I wasn't really thinking about the issues it would cause if your actual table included additional columns (which is more than likely the case).

Anyways, glad to hear it worked out for you. Cheers!