_x000D_
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)In the question table, sales info is provided. Add subtotals and grand totals into the data like the result table.
📌 Challenge Details and Links
Challenge Number: 88
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Subtotal Calculation! with Power Query
Power Query solution 1 for Subtotal Calculation!, proposed by Zoran Milokanović:
let
Source = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
"Total Regions",
each List.Sum(List.Skip(Record.ToList(_), 2))
),
F = each
let
t = Table.ToColumns(Table.SelectRows(Source, (r) => _ = null or r[Product] = _))
in
{{"Total " & t{0}{0}, "Grand Total"}{Byte.From(_ = null)}, null}
& List.Transform(List.Skip(t, 2), List.Sum),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Source),
each {_}
& {{}, {F(_{0})}}{Byte.From(_{1} = 4)}
& {{}, {F(null)}}{Byte.From(_ = Record.ToList(Table.Last(Source)))},
(i, _) => _
),
Table.ColumnNames(Source)
)
in
S
Power Query solution 2 for Subtotal Calculation!, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddColumn(_,"Total Regions", each List.Sum(List.RemoveFirstN(Record.FieldValues(_),2))),
b =
hashtag
#table(Table.ColumnNames(a),{List.Transform(Table.ToColumns(a),each try List.Sum(_) otherwise "Total " & a[Product]{0})}),
c = a & Table.ReplaceValue(b,each [Season], null, Replacer.ReplaceValue,{"Season"} )
in c }})[tab],
total =
let
a = List.Transform(Table.ToColumns(Table.Combine(List.Transform(gp, each Table.SelectRows(_, each Text.StartsWith([Product],"Total" ) )))), each try List.Sum(_) otherwise "Grand Total" ),
b = Table.FromRows({a}, Table.ColumnNames(gp{0}))
in b,
cmb = Table.Combine(gp) & total
in
cmb
Power Query solution 3 for Subtotal Calculation!, proposed by Rafael González B.:
let
Tbl = Excel.CurrentWorkbook(){0}[Content],
Grp = Table.Group(Tbl, {"Product"},
{
{"T",
each
let
a = Table.AddColumn(_, "Total Regions", each [Region 1] + [Region 2]),
N = Table.ColumnNames(a),
b = List.Accumulate(
List.LastN(N, 3),
{},
(s , c) => s & {List.Sum(Table.Column(a, c))}),
c = {"Total " & a{0}[Product], null} & b,
d = Table.FromRows(Table.ToRows(a) & {c}, N)
in
d}
}),
GT =
let
LS = List.Sum,
R1 = {LS(Tbl[Region 1])},
R2 = {LS(Tbl[Region 2])},
RT = {LS(R1 & R2)},
LC = List.Combine({{"Grand Total", null},R1, R2, RT})
in
Table.FromRows({LC}, Table.ColumnNames(Tbl) & {"Total Regions"}),
Result = Table.Combine(Grp[T] & {GT})
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Subtotal Calculation!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Combine(Table.Group(Source, {"Product"}, {{"All", each
let
a = _,
b = Table.ToColumns(a),
c = List.Skip(b,2),
d = {"Total "&a[Product]{0}}&{null}&List.Transform(c, each List.Sum(_)),
e = Table.ToRows(a)&{d},
f = List.Transform(e, each List.Sum(List.Skip(_,2))),
g = List.Transform({0..List.Count(e)-1}, each e{_}&{f{_}}),
h = Table.FromRows(g, Table.ColumnNames(a)&{"Total Regions"})
in h}})[All]),
GT = {"Grand Total", null}&List.Transform(List.Skip(Table.ToColumns(Table.SelectRows(Group,
each Text.Contains([Product], "Total"))), 2), List.Sum),
Sol = Group & Table.FromRows({GT}, Table.ColumnNames(Group))
in
Sol
Power Query solution 5 for Subtotal Calculation!, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Region 1", each List.Sum([Region 1])}, {"Region 2", each List.Sum([Region 2])}}
),
AddT = Table.TransformColumns(Group, {"Product", each _ & " T"}),
Combine = Table.Combine({Source, AddT}),
RepNull = Table.ReplaceValue(Combine, null, "", Replacer.ReplaceValue, {"Season"}),
Sort = Table.Sort(RepNull, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
TReg = Table.AddColumn(Sort, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
SubT = Table.TransformColumns(
TReg,
{"Product", each if Text.Length(_) > 1 then _ & "otal" else _}
),
GT = Table.InsertRows(
SubT,
Table.RowCount(SubT),
{
[
Product = "Grand Total",
Season = null,
Region 1 = List.Sum(Source[Region 1]),
Region 2 = List.Sum(Source[Region 2]),
Total Regions = List.Sum(List.Combine(List.Skip(Table.ToColumns(Source), 2)))
]
}
)
in
GT
Power Query solution 6 for Subtotal Calculation!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Product"}, {{"Table", each _}}),
ProductTotals = Table.TransformColumns(
Group,
{
"Table",
each _
& Table.FromRecords(
{
[
Product = "Total " & [Product]{0},
Region 1 = List.Sum([Region 1]),
Region 2 = List.Sum([Region 2])
]
}
)
}
),
Expand = Table.ExpandTableColumn(
Table.SelectColumns(ProductTotals, {"Table"}),
"Table",
{"Product", "Season", "Region 1", "Region 2"},
{"Product", "Season", "Region 1", "Region 2"}
),
GrandTotal = Expand
& Table.FromRecords(
{
[
Product = "Grand Total",
Region 1 = List.Sum(Expand[Region 1]) / 2,
Region 2 = List.Sum(Expand[Region 2]) / 2
]
}
),
TotalRegions = Table.AddColumn(GrandTotal, "Total Regions", each [Region 1] + [Region 2])
in
TotalRegionsPower Query solution 7 for Subtotal Calculation!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "Total Regions", each List.Sum(List.Skip(Record.ToList(_), 2))),
B = Table.Group(
A,
{"Product"},
{
{"Region 1", each List.Sum([Region 1]), type number},
{"Region 2", each List.Sum([Region 2]), type number},
{"Total Regions", each List.Sum([Total Regions]), type number}
}
),
C = Table.AddColumn(B, "Season", each List.Max(A[Season]) + 1),
D = Table.Combine({A, C}),
E = Table.Sort(D, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
F = Table.Group(
E,
{},
{
{"Region 1", each List.Sum([Region 1]) / 2, type number},
{"Region 2", each List.Sum([Region 2]) / 2, type number},
{"Total Regions", each List.Sum([Total Regions]) / 2, type number}
}
),
G = Table.AddColumn(F, "Product", each "Grand Total"),
H = Table.Combine({D, G}),
I = Table.Sort(H, {{"Product", Order.Ascending}, {"Season", Order.Ascending}}),
J = Table.AddColumn(I, "P", each if [Season] = 5 then "Total" & [Product] else [Product]),
K = Table.SelectColumns(J, {"P", "Season", "Region 1", "Region 2", "Total Regions"}),
L = Table.RenameColumns(K, {{"P", "Product"}})
in
L
Power Query solution 8 for Subtotal Calculation!, proposed by Szabolcs Phraner:
let
Source = ...,
SetDataTypes = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {
_,
if _ = "Product" then Text.From else Number.From,
if _ = "Product" then type text else type number
}
)
),
Total_Regions = Table.AddColumn(
SetDataTypes,
"Total Regions",
each List.Sum(List.Skip(Record.FieldValues(_), 2)),
type number
),
RegionColumns = List.Skip(Table.ColumnNames(Total_Regions), 2),
CreateTotalRow = (Tbl as table, TotalColumns as list, PlusFields as nullable record) =>
let
RC = Table.RowCount(Tbl),
TotalRow = PlusFields
& Record.FromList(
List.Transform(TotalColumns, each List.Sum(Table.Column(Tbl, _))),
TotalColumns
)
in
Table.InsertRows(Tbl, RC, {TotalRow}),
Grand_Total = {
Table.Last(CreateTotalRow(Total_Regions, RegionColumns, [Product = "Grand Total", Season = ""]))
},
SubTotals = Table.Group(
Total_Regions,
{"Product"},
{
{
"Subtotals",
each Table.ToRecords(
CreateTotalRow(_, RegionColumns, [Product = "Total " & Table.FirstValue(_), Season = ""])
)
}
}
)[Subtotals],
CombineRecords = Table.FromRecords(
List.Combine(SubTotals & {Grand_Total}),
Value.Type(Total_Regions)
)
in
CombineRecords
Solving the challenge of Subtotal Calculation! with Excel
Excel solution 1 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(g,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
p,
TAKE(
g,
,
1
),HSTACK(IF((INDEX(
g,
,
2
)="")*(p<>"Grand Total"),
"Total ",
"")&p,
DROP(
g,
,
1
),
VSTACK(
"Total Regions",
BYROW(
DROP(
g,
1,
2
),
SUM
)
)))
Excel solution 2 for Subtotal Calculation!, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E18,
w,
HSTACK(
z,
BYROW(
DROP(
z,
,
2
),
SUM
)
),
r,
SEQUENCE(
ROWS(
z
)
),
p,
TAKE(
z,
,
1
), SORTBY(
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
w,
REDUCE(
HSTACK(
"Grand Total",
""
),
UNIQUE(
p
),
LAMBDA(
a,
v,
LET(
b,
LAMBDA(
i,
y,
BYCOL(
DROP(
w,
,
i
)*y,
LAMBDA(
x,
SUM(
x
)
)
)
),
IFNA(
VSTACK(
a,
HSTACK(
"Total "&v,
"",
b(
2,
p=v
)
)
),
b(
0,
1
)
)
)
)
)
),
VSTACK(
0,
r,
99,
FILTER(
r,
p<>DROP(
VSTACK(
p,
0
),
1
)
)
)
)
)
Excel solution 3 for Subtotal Calculation!, proposed by محمد حلمي:
=LET(
r,
SUM(
D3:D18
),
w,
SUM(
E3:E18
),
VSTACK( REDUCE(
I2:M2,
UNIQUE(
B3:B18
),
LAMBDA(
a,
v,
LET(
e,
FILTER(
C3:E18,
B3:B18=v
),
i,
HSTACK(
e,
TAKE(
e,
,
-1
)+INDEX(
e,
,
2
)
),
VSTACK(
a,
IFNA(
HSTACK(
v,
i
),
v
),
HSTACK(
"Total "&v,
"",
BYCOL(
DROP(
i,
,
1
),
LAMBDA(
q,
SUM(
q
)
)
)
)
)
)
)
), HSTACK(
"Grand Total",
"",
r,
w,
r+w
)
)
)
Excel solution 4 for Subtotal Calculation!, proposed by Oscar Mendez Roca Farell:
=LET(
p,
B3:B18,
s,
TOROW(
C3:C6
)^0,
t,
"Total ",
r,
REDUCE(
I2:M2,
UNIQUE(
p
),
LAMBDA(
i,
x,
LET(
f,
FILTER(
B3:E18,
p=x
),
e,
DROP(
f,
,
2
),
m,
MMULT(
s,
e
),
VSTACK(
i,
IFNA(
VSTACK(
HSTACK(
f,
MMULT(
e,
{1; 1}
)
),
HSTACK(
t&x,
"",
m
)
),
SUM(
m
)
)
)
)
)
),
VSTACK(
r,
HSTACK(
"Grant"&t,
"",
MMULT(
s,
FILTER(
DROP(
r,
,
2
),
INDEX(
r,
,
2
)=""
)
)
)
)
)
Excel solution 5 for Subtotal Calculation!, proposed by Owen Price:
=LET(
group_by,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
), product,
TAKE(
group_by,
,
1
), rename_subtotals,
IF(
(CHOOSECOLS(
group_by,
2
) = "") * (product <> "Grand Total"), "Total ", ""
) & product, HSTACK( rename_subtotals, DROP(
group_by,
,
1
), VSTACK(
"Total Regions",
BYROW(
DROP(
group_by,
1,
2
),
SUM
)
) )
)
Excel solution 6 for Subtotal Calculation!, proposed by Julian Poeltl:
=LET(
T,
D3:E18,
A,
B3:B18,
U,
UNIQUE(
A
),
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
U,
LAMBDA(
B,
TEXTJOIN(
",",
0,
LET(
F,
FILTER(
B3:E18,
A=B
),
VSTACK(
F,
HSTACK(
"Total "&B,
"",
SUM(
CHOOSECOLS(
F,
3
)
),
SUM(
CHOOSECOLS(
F,
4
)
)
)
)
)
)
)
)
),
","
),
4
),
C,
IFERROR(
R*1,
R
),
VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
C,
BYROW(
TAKE(
C,
,
-2
),
LAMBDA(
A,
SUM(
A
)
)
)
),
HSTACK(
"Grand Total",
"",
BYCOL(
T,
LAMBDA(
A,
SUM(
A
)
)
),
SUM(
T
)
)
)
)
Excel solution 7 for Subtotal Calculation!, proposed by Kris Jaganah:
=LET(a,
GROUPBY(
B2:C18,
D2:E18,
SUM,
3,
2
),
b,
VSTACK(
"Total Regions",
BYROW(
DROP(
a,
1,
2
),
SUM
)
),
c,
TAKE(
a,
,
1
),
d,
IF((CHOOSECOLS(
a,
2
)="")*(LEN(
c
)=1),
"Total "&c,
c),
HSTACK(
d,
DROP(
a,
,
1
),
b
))
Excel solution 8 for Subtotal Calculation!, proposed by John Jairo Vergara Domínguez:
=LET(i,
GROUPBY(
B2:C18,
HSTACK(
D2:E18,
D2:D18+E2:E18
),
SUM,
3,
2
),
t,
"Total ",
p,
TAKE(
i,
,
1
),
HSTACK(IF((INDEX(
i,
,
2
)="")*(LEN(
p
)=1),
t&p,
p),
IFERROR(
DROP(
i,
,
1
),
t&"Regions"
)))
Excel solution 9 for Subtotal Calculation!, proposed by Imam Hambali:
=LET( a,
GROUPBY(
B3:C18,
HSTACK(
D3:D18,
E3:E18
),
SUM,
,
2
), b,
BYROW(
DROP(
a,
,
2
),
LAMBDA(
x,
SUM(
x
)
)
), c,
MAP(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
LAMBDA(
x,
y,
IF(
AND(
y="",
x<>"Grand Total"
),
"Total "&x,
x
)
)
), VSTACK(
HSTACK(
B2:E2,
"Total Regions"
),
HSTACK(
c,
DROP(
a,
,
1
),
b
)
))Excel solution 10 for Subtotal Calculation!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
B3:B18
), LET( _t,
REDUCE(
HSTACK(
B2:E2,
"Total Regions"
),
_u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_f,
FILTER(
B3:E18,
B3:B18 = y
),
_f1,
TAKE(
_f,
,
-2
),
_sh,
BYCOL(
_f1,
LAMBDA(
a,
SUM(
a
)
)
),
_sv,
BYROW(
_f1,
LAMBDA(
b,
SUM(
b
)
)
),
VSTACK(
HSTACK(
_f,
_sv
),
HSTACK(
{"Total A",
""},
_sh,
SUM(
_sh
)
)
)
)
)
)
), _t1,
BYCOL(
FILTER(
TAKE(
_t,
,
-3
),
ISNUMBER(
SEARCH(
"Total",
TAKE(
_t,
,
1
)
)
)
),
LAMBDA(
a,
SUM(
a
)
)
), VSTACK(
_t,
HSTACK(
{"Grand Total",
""},
_t1
)
) ))
Excel solution 11 for Subtotal Calculation!, proposed by Asheesh Pahwa:
=LET(
r,
REDUCE(
HSTACK(
B2:E2,
M2
),
UNIQUE(
B3:B18
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:E18,
B3:B18=y
),
h,
CHOOSECOLS(
f,
2,
3
),
br,
BYROW(
h,
LAMBDA(
a,
SUM(
a
)
)
),
s,
SUM(
br
),
vs,
VSTACK(
br,
s
),
bc,
BYCOL(
h,
LAMBDA(
v,
SUM(
v
)
)
),
HSTACK(
VSTACK(
IFNA(
HSTACK(
y,
f
),
y
),
HSTACK(
"Total "&y,
"",
bc
)
),
vs
)
)
)
)
), s,
ISNUMBER(
SEARCH(
"Total",
TAKE(
r,
,
1
)
)
),
f,
FILTER(
CHOOSECOLS(
r,
{3,
4,
5}
),
s
), VSTACK(
r,
HSTACK(
"Grand Tota",
"",
BYCOL(
f,
LAMBDA(
x,
SUM(
x
)
)
)
)
)
)