Treat the data between two pluses as one group. Find the sum between two pluses and also give the index number of those groups.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 640
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum Between Plus Groups with Power Query
Power Query solution 1 for Sum Between Plus Groups, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Data],
_ = Table.FromRows(
List.Accumulate(
Source,
{},
(b, n) =>
let
l = List.Last(b, {0}),
f = Byte.From(n = "+")
in
List.RemoveLastN(b, 1 - f) & {{l{0} + f, {l{1} + n, 0}{f}}}
),
{"Group", "Sum"}
)
in
_Power Query solution 2 for Sum Between Plus Groups, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data],
B = List.PositionOf(A, "+", 2),
C = Table.FromRows(
List.Transform(
List.Positions(B),
each {_ + 1, List.Sum(List.Range(A, B{_} + 1, try B{_ + 1} - B{_} - 1 otherwise List.Last(B)))}
),
{"Group", "Sum"}
)
in
CPower Query solution 3 for Sum Between Plus Groups, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(A){0},
C = List.Accumulate(
B,
{},
(x, y) => x & {if y = "+" then List.Last(x, 0) + 1 else List.Last(x, 0)}
),
D = Table.FromRows(
List.Transform(
List.Distinct(C),
each {_, List.Sum(List.Skip(List.Zip(List.Select(List.Zip({C, B}), (v) => v{0} = _)){1}))}
),
{"Group", "Sum"}
)
in
DPower Query solution 4 for Sum Between Plus Groups, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.AddColumn(S, "T", each if [Data] is number then 1 else 0),
b = Table.Group(a, "T", {"Sum", each List.Sum([Data])}, 0),
c = Table.AddIndexColumn(Table.SelectRows(b, each [T] = 1), "Group", 1),
Sol = Table.SelectColumns(c, {"Group", "Sum"})
in
SolPower Query solution 5 for Sum Between Plus Groups, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data],
Lst = List.Accumulate(
List.Skip(Source),
{[Group = 1, Sum = 0]},
(a, v) =>
if v = "+" then
a & {[Group = List.Last(a)[Group] + 1, Sum = 0]}
else
List.RemoveLastN(a) & {[Group = List.Last(a)[Group], Sum = List.Last(a)[Sum] + v]}
),
Res = Table.FromRecords(Lst)
in
ResPower Query solution 6 for Sum Between Plus Groups, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, "Data", {"Sum", Func}, 0, (x, y) => Number.From(y = "+")),
Func = each [A = List.Transform([Data], each if _ is number then _ else 0), B = List.Sum(A)][B],
Res = Table.AddIndexColumn(Group, "Group", 1)[[Group], [Sum]]
in
ResPower Query solution 7 for Sum Between Plus Groups, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data],
B = List.PositionOf(A, "+", 2) & {List.Count(A)},
C = Table.FromRows(
List.Generate(
() => 0,
each _ < List.Count(B) - 1,
each _ + 1,
each {_ + 1, List.Sum(List.Transform({B{_} + 1 .. B{_ + 1} - 1}, each A{_}))}
),
{"Group", "Sum"}
)
in
CPower Query solution 8 for Sum Between Plus Groups, proposed by Krupesh Bhansali:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Conditional Column" = Table.AddColumn(
Source,
"Custom",
each if [Data] = "+" then 1 else null
),
#"Grouped Rows" = Table.Group(
#"Added Conditional Column",
{"Custom"},
{{" Expeceted Sum", each List.Sum([Data])}},
GroupKind.Local
),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Custom] = null)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Answer Group", 1, 1, Int64.Type),
#"Removed Columns" = Table.SelectColumns(#"Added Index", {"Answer Group", " Expeceted Sum"})
in
#"Removed Columns"Solving the challenge of Sum Between Plus Groups with Excel
Excel solution 1 for Sum Between Plus Groups, proposed by Bo Rydobon 🇹🇭:
=LET(z,A3:A21,GROUPBY(SCAN(0,z>"",SUM),z,SUM,0,0))Excel solution 2 for Sum Between Plus Groups, proposed by Rick Rothstein:
=LET(
m,
MAP(
TEXTSPLIT(
TEXTJOIN(
"+",
,
A4:A23
),
,
"+++"
),
LAMBDA(
x,
SUM(
0+TEXTSPLIT(
x,
"+"
)
)
)
),
HSTACK(
SEQUENCE(
ROWS(
m
)
),
m
)
)Excel solution 3 for Sum Between Plus Groups, proposed by John V.:
=GROUPBY(SCAN(0,A3:A21=A3,SUM),N(+A3:A21),SUM,,0)Excel solution 4 for Sum Between Plus Groups, proposed by Kris Jaganah:
=LET(
a,
A3:A21,
GROUPBY(
MAP(
a,
LAMBDA(
x,
SUM(
N(
A3:x="+"
)
)
)
),
a,
SUM,
0,
0
)
)Excel solution 5 for Sum Between Plus Groups, proposed by Alejandro Campos:
=LET(
r,
A3:A21,
s,
SCAN(
0,
r,
LAMBDA(
x,
y,
IF(
y = "+",
x + 1,
x
)
)
),
HSTACK(
SEQUENCE(
ROWS(
UNIQUE(
s
)
)
),
MAP(
UNIQUE(
s
),
LAMBDA(
x,
SUM(
FILTER(
r,
s = x
)
)
)
)
)
)Excel solution 6 for Sum Between Plus Groups, proposed by Timothée BLIOT:
=LET(A,A3:A21,GROUPBY(SCAN(0,A="+",LAMBDA(w,v,IF(v,w+1,w))),A,SUM,0,0))Excel solution 7 for Sum Between Plus Groups, proposed by Hussein SATOUR:
=LET(a,SCAN(0,A3:A21,LAMBDA(x,y,IF(y="+",-x,y)+x)),b,FILTER(a,VSTACK(DROP(a,1),0)=0),HSTACK(SEQUENCE(ROWS(b)),b))Excel solution 8 for Sum Between Plus Groups, proposed by Oscar Mendez Roca Farell:
=GROUPBY(SCAN(0,A3:A21>"",SUM),N(+A3:A21),SUM,,0)Excel solution 9 for Sum Between Plus Groups, proposed by Duy Tùng:
=GROUPBY(SCAN(0,A3:A21="+",SUM),N(+A3:A21),SUM,,0)Excel solution 10 for Sum Between Plus Groups, proposed by Sunny Baggu:
=HSTACK(
SEQUENCE(SUM(N(A3:A21 = "+"))),
BYROW(
DROP(
TEXTSPLIT(ARRAYTOTEXT(A3:A21), ", ", "+"),
1,
1
),
LAMBDA(a, SUM(TOROW(--a, 3)))
)
)Excel solution 11 for Sum Between Plus Groups, proposed by Md. Zohurul Islam:
=LET(
z,A3:A21,
a,SCAN(0,ABS(z="+"),SUM),
hdr,HSTACK("Group","Sum"),
REDUCE(hdr,UNIQUE(a),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(z,a=y))))))
)Excel solution 12 for Sum Between Plus Groups, proposed by Pieter de B.:
=GROUPBY(SCAN(0,A3:A21="+",SUM),A3:A21,SUM,0,0)Excel solution 13 for Sum Between Plus Groups, proposed by ferhat CK:
=LET(r,A3:A21,a,SCAN(0,r,LAMBDA(x,y,IF(y="+",x+1,x))),DROP(REDUCE(0,UNIQUE(a),LAMBDA(x,y,VSTACK(x,HSTACK(ROWS(x),SUM(FILTER(r,a=y)))))),1))
=LET(a,DROP(MAP(TEXTSPLIT(ARRAYTOTEXT(A3:A21),,"+"),LAMBDA(x,SUM(TOCOL(--TEXTSPLIT(x,"; "),3)))),1),HSTACK(SEQUENCE(ROWS(a)),a))Excel solution 14 for Sum Between Plus Groups, proposed by Imam Hambali:
=LET(
d, A3:A21,
VSTACK({"Group","Sum"}, GROUPBY(SCAN(0,IF(d="+",1,0), LAMBDA(x,y, IF(y=1,y+x,x))),d,SUM,0,0))
)Excel solution 15 for Sum Between Plus Groups, proposed by Gerson Pineda:
=LET(
d,
A3:A21,
GROUPBY(
MAP(
d,
LAMBDA(
x,
SUM(
N(
x:A3="+"
)
)
)
),
d,
SUM,
0,
0
)
)Excel solution 16 for Sum Between Plus Groups, proposed by Milan Shrimali:
=LET(
RNNG,
A2:A11,
RWS,
HSTACK(
ARRAYFORMULA(
ROW(
RNNG
)
),
RNNG
),
UNQ,
FILTER(
ROW(
RNNG
),
RNNG="+"
),
LUKP,
BYROW(
RWS,
LAMBDA(
Y,
HSTACK(
Y,
XLOOKUP(
CHOOSECOLS(
Y,
1
),
UNQ,
UNQ,
,
-1
)
)
)
),
BYROW(
UNQ,
LAMBDA(
Z,
SUM(
FILTER(
IFERROR(
--CHOOSECOLS(
LUKP,
2
),
0
),
CHOOSECOLS(
LUKP,
3
)=Z
)
)
)
)
)Excel solution 17 for Sum Between Plus Groups, proposed by Peter Bartholomew:
= LET(
grp,
SCAN(
0,
data="+",
SUM
),
GROUPBY(
grp,
data,
SUM,
0,
0
)
)Excel solution 18 for Sum Between Plus Groups, proposed by Ahmed Ariem:
=LET(b,
A3:A21,
a,
IF(b>"",
"",
SCAN(0,
(b>"")*1,
SUM)),
GROUPBY(
FILTER(
a,
a<>""
),
FILTER(
b,
a<>""
),
SUM
))Excel solution 19 for Sum Between Plus Groups, proposed by Nicolas Micot:
=LET(
_data;
A3:A21;
_indexes;
SCAN(
0;
_data;
LAMBDA(
l_value;
l_data;
l_value+SI(
l_data="+";
1;
0
)
)
);
_indexGroups;
UNIQUE(
_indexes
);
_sumByGroup;
MAP(
_indexGroups;
LAMBDA(
l_groupIndex;
SOMME(
FILTRE(
_data;
_indexes=l_groupIndex
)
)
)
);
ASSEMB.H(
_indexGroups;
_sumByGroup
)
)Excel solution 20 for Sum Between Plus Groups, proposed by El Badlis Mohd Marzudin:
=LET(x,A3:A21,GROUPBY(SCAN(0,x,LAMBDA(a,x,IF(x="+",a+1,a))),IF(x>"",0,x),SUM,,0))Excel solution 21 for Sum Between Plus Groups, proposed by Hussain Ali Nasser:
=LET(sum,MAP(TEXTSPLIT(TEXTJOIN("|",TRUE,A3:A21),,"+|",TRUE),LAMBDA(x,SUM(TEXTSPLIT(x,"|",,TRUE)+0))),HSTACK(SEQUENCE(ROWS(sum)),sum))Excel solution 22 for Sum Between Plus Groups, proposed by Jorge Alvarez:
=LET(
_acum;
SCAN(
0;
A3:A25;
LAMBDA(
_a;
_v;
SI(
_v="+";
0;
_a+_v
)
)
);
_i;
SECUENCIA(
FILAS(
_acum
)
)+1;
_re;
MAP(
_i;
LAMBDA(
_ind;
SI.CONJUNTO(
O(
INDICE(
_acum;
_ind
)=0;
INDICE(
_acum;
_ind
)=""
);
INDICE(
_acum;
_ind-1
)
)
)
);
_re2;
APILARV(
ENCOL(
_re;
2
);
TOMAR&(
_acum;
-1
)
);
_valores;
FILTRAR(
_re2;
_re2>0
);
_g;
SECUENCIA(
FILAS(
_valores
)
);
APILARH(
_g;
_valores
)
)Excel solution 23 for Sum Between Plus Groups, proposed by Tomasz Jakóbczyk:
=SCAN(
0;
A3:A21;
LAMBDA(
t;
v;
IF(
v="+";
0;
SUM(
t+v
)
)
)
)
C3:
=HSTACK(
SEQUENCE(
COUNTIF(
A3:A21;
"+"
)
);
DROP(
FILTER(
OFFSET(
F2;
;
;
ROWS(
A3:A21
)+1;
);
VSTACK(
A3:A21;
"+"
)="+"
);
1
)
)Excel solution 24 for Sum Between Plus Groups, proposed by Fredrick Nwanyanwu:
=LET(
a,
SCAN(
0,
A3:A21,
LAMBDA(
b,
c,
IF(
c="+",
0,
b+c
)
)
),
d,
DROP(
VSTACK(
A3:A21,
"+"
),
1
),
e,
HSTACK(
a,
d
),
f,
FILTER(
e,
d="+"
),
g,
DROP(
f,
,
-1
),
h,
HSTACK(
SEQUENCE(
COUNTA(
g
)
),
g
),
r,
h,
r
)Excel solution 25 for Sum Between Plus Groups, proposed by Ana Di Nezio Pérez:
=LET(
s, SCAN(0, A3:A21, LAMBDA(a,b, IF(b="+", 0, a+b))),
helper, DROP(VSTACK(A3:A21, "+"),1),
comb, HSTACK(helper, s),
filtered, DROP(FILTER(comb, helper="+"),,1),
seq, SEQUENCE(ROWS(filtered)),
HSTACK(seq,filtered)
)Excel solution 26 for Sum Between Plus Groups, proposed by Enrico Mendiola:
=LET(
_h,{"Group","Sum"},
_range, IF(ISNUMBER(A3:A21),A3:A21,""),
_isNum, LAMBDA(v, ISNUMBER(v)),
_prevGroup, LAMBDA(a,r, IF(_isNum(r), a, a + 1)),
_groups, SCAN(0, _range, LAMBDA(g,v, IF(_isNum(v), IF(g=0, 1, g), _prevGroup(g, v)))),
_filteredGroups, IF(_isNum(_range), _groups, ""),
_hs,FILTER(HSTACK(_range,_filteredGroups),_range<>""),
VSTACK(_h,GROUPBY(CHOOSECOLS(_hs,2),CHOOSECOLS(_hs,1),SUM,,0))
)Solving the challenge of Sum Between Plus Groups with Python
Python solution 1 for Sum Between Plus Groups, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=20)
test = pd.read_excel(path, usecols="C:D", skiprows=1, nrows=6)
input['Group'] = (input['Data'] == '+').cumsum().astype('int64')
filtered = input[input['Data'] != '+']
result = filtered.groupby('Group').agg({'Data': lambda x: x.astype(float).sum()}).reset_index()
result.columns = ['Group', 'Sum']
result['Sum'] = result['Sum'].astype('int64')
print(result.equals(test)) # True
Solving the challenge of Sum Between Plus Groups with Python in Excel
Python in Excel solution 1 for Sum Between Plus Groups, proposed by Alejandro Campos:
df = xl("A2:A21", headers=True)
sums, indices, group_index, current_sum = [], [], 0, 0
for i, v in enumerate(df['Data']):
if v == '+':
if current_sum: sums.append(current_sum); indices.append(group_index); current_sum = 0
group_index += 1
elif isinstance(v, int): current_sum += v
if current_sum: sums.append(current_sum); indices.append(group_index)
result_df = pd.DataFrame({'Group': indices, 'Sum': sums})
Python in Excel solution 2 for Sum Between Plus Groups, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:A21", True)
result = (
df.groupby((df["Data"] == "+").cumsum())
.agg(Sum=("Data", lambda x: x[1:].sum()))
.reset_index()
)
result
Solving the challenge of Sum Between Plus Groups with R
R solution 1 for Sum Between Plus Groups, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A2:A21")
test = read_excel(path, range = "C2:D8")
result = input %>%
mutate(index = cumsum(Data == "+")) %>%
filter(Data != "+") %>%
summarise(sum = sum(as.numeric(Data)), .by = index)
all.equal(result$sum, test$Sum)
#> [1] TRUE
&&