# Spill range help

#### razaas

##### Member
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))

With Excel 2021, you could try the following:

Code:
``````=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:

Code:
``````=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)),
row_nums, SEQUENCE(ROWS(body) + 2, , 0),
IFERROR(IF(row_nums, INDEX(body, row_nums, {1,2}), head), foot)
)``````

With Excel 2021, you could try the following:

Code:
``````=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:

Code:
``````=LET(
pm, SORT(UNIQUE(Table3[Payment Mode])),
amt, SUMIFS(Table3[Amount], Table3[Payment Mode], pm),