This problem is contributed by Mehmet Çiçek. Find the total spend corresponding for the date range given in columns F & G. Ex. For range 1-Jun-2015 to 10-Jun-2015 From 1-Jun-2015 to 3-Jun-2015, rate is 5 & people 8. Hence total for 3 days = 3 * 5 * 8 = 120 From 4-Jun-2015 to 10-Jun-2015, rate is 10 & people 12. Hence total for 7 days = 7 * 10 * 12 = 840 Hence total = 120 + 840 = 960
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 215
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Date Range Spend Total with Power Query
Power Query solution 1 for Date Range Spend Total, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.Buffer(
Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Ex",
each [Rate] * [No of People]
)
),
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Spend = Table.AddColumn(
T2,
"Total Spend",
each List.Sum(
List.Transform(
List.Dates(
Date.From([From Date]),
Number.From([To Date] - [From Date]) + 1,
Duration.From(1)
),
(d) =>
List.Sum(
Table.SelectRows(
Source,
each [From Date] <= DateTime.From(d) and DateTime.From(d) <= [To Date]
)[Ex]
)
)
)
)
in
SpendPower Query solution 2 for Date Range Spend Total, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Solution = Table.AddColumn(
Source,
"Total Spend",
each List.Sum(
List.Transform(
{Number.From([From Date]) .. Number.From([To Date])},
each
let
d = Date.From(_),
r = Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
each d >= DateTime.Date([From Date]) and d <= DateTime.Date([To Date])
)
in
r[Rate]{0} * r[No of People]{0}
)
)
)
in
SolutionPower Query solution 3 for Date Range Spend Total, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Dias = Table.AddColumn(Table1, "Dias", each {Number.From([From Date]) .. Number.From([To Date])}),
CalDiario = Table.AddColumn(Dias, "Diario", each [No of People] * [Rate])[[Dias], [Diario]],
TablaDias = Table.ExpandListColumn(CalDiario, "Dias"),
Zip = List.Zip({TablaDias[Dias], TablaDias[Diario]}),
Sol = Table.AddColumn(
Table2,
"Total Spend",
each
let
a = {Number.From([From Date]) .. Number.From([To Date])},
b = List.Sum(List.ReplaceMatchingItems(a, Zip))
in
b
)
in
SolSolving the challenge of Date Range Spend Total with Excel
Excel solution 1 for Date Range Spend Total, proposed by Bo Rydobon 🇹🇭:
=MAP(F2:F4,
G2:G4,
LAMBDA(f,
g,
SUM(MAP(SEQUENCE(
g-f+1,
,
f
),
LAMBDA(d,
SUM((d>=A2:A6)*(d<=B2:B6)*C2:C6*D2:D6))))))Excel solution 2 for Date Range Spend Total, proposed by Bo Rydobon 🇹🇭:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
f,
g,
SUM(
LOOKUP(
SEQUENCE(
g-f+1,
,
f
),
A2:A6,
C2:C6*D2:D6
)
)
)
)Excel solution 3 for Date Range Spend Total, proposed by Rick Rothstein:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
f,
g,
SUM(
C2:C6*D2:D6*IFERROR(
MAP(
A2:A6,
B2:B6,
LAMBDA(
a,
b,
ROWS(
INDIRECT(
a&":"&b
) INDIRECT(
f&":"&g
)
)
)
),
0
)
)
)
)Excel solution 4 for Date Range Spend Total, proposed by John V.:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
a,
b,
LET(
f,
LAMBDA(
x,
LOOKUP(
SEQUENCE(
1+b-a,
,
a
),
A2:x
)
),
SUM(
f(
C6
)*f(
D6
)
)
)
)
)
✅=MAP(
F2:F4,
G2:G4,
LAMBDA(
a,
b,
SUM(
LOOKUP(
SEQUENCE(
1+b-a,
,
a
),
A2:A6,
C2:C6*D2:D6
)
)
)
)Excel solution 5 for Date Range Spend Total, proposed by محمد حلمي:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
a,
b,
LET(
r,
SEQUENCE(
B6-A2+1,
,
A2
),
SUM(
XLOOKUP(
SEQUENCE(
b-a+1,
,
a
),
r,
XLOOKUP(
r,
A2:A6,
C2:C6*D2:D6,
,
-1
)
)
)
)
)
)Excel solution 6 for Date Range Spend Total, proposed by محمد حلمي:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
a,
b,
LET(
n,
REDUCE(
0,
A2:A6,
LAMBDA(
a,
d,
LET(
r,
SEQUENCE(
OFFSET(
d,
,
1
)-d+1,
,
d
),
VSTACK(
a,
HSTACK(
r,
r^0*OFFSET(
d,
,
2
),
r^0*OFFSET(
d,
,
3
)
)
)
)
)
),
SUM(
XLOOKUP(
SEQUENCE(
b-a+1,
,
a
),
TAKE(
n,
,
1
),
INDEX(
n,
,
2
)*TAKE(
n,
,
-1
),
0
)
)
)
)
)Excel solution 7 for Date Range Spend Total, proposed by Kris Jaganah:
=LET(a,
A2:A6,
b,
B2:B6,
c,
C2:C6*D2:D6,
d,
F2:F4,
e,
G2:G4,
f,
MIN(
a
),
g,
SEQUENCE(
MAX(
b
)-f+1,
,
f
),
h,
MAP(g,
LAMBDA(x,
SUM((x>=a)*(x<=b)*c))),
MAP(d,
e,
LAMBDA(y,
z,
SUM(FILTER(h,
(g>=y)*(g<=z))))))Excel solution 8 for Date Range Spend Total, proposed by Julian Poeltl:
=LET(
F,
A2:A6,
C,
C2:C6*D2:D6,
MAP(
F2:F4,
G2:G4,
LAMBDA(
A,
B,
SUM(
XLOOKUP(
SEQUENCE(
1+B-A,
,
A
),
F,
C,
,
-1
)
)
)
)
)Excel solution 9 for Date Range Spend Total, proposed by Timothée BLIOT:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
x,
y,
SUM(
XLOOKUP(
SEQUENCE(
y-x+1,
,
x
),
B2:B6,
C2:C6*D2:D6,
,
1
)
)
)
)Excel solution 10 for Date Range Spend Total, proposed by Oscar Mendez Roca Farell:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
fd,
td,
LET(
_f,
LAMBDA(
i,
BYROW(
A2:B6,
LAMBDA(
r,
MEDIAN(
i,
r
)
)
)
),
_m,
FILTER(
HSTACK(
_f(
td
),
_f(
fd
),
C2:C6*D2:D6
),
_f(
fd
)-_f(
td
)<0
),
SUM(
BYROW(
TAKE(
_m,
,
2
),
LAMBDA(
j,
SUM(
j*{1,
-1}
)+1
)
)*TAKE(
_m,
,
-1
)
)
)
)
)Excel solution 11 for Date Range Spend Total, proposed by Sunny Baggu:
=MAP(
F2:F4,
G2:G4,
LAMBDA(a,
b,
LET(
_seq,
SEQUENCE(
b - a + 1,
,
a
),
_e1,
LAMBDA(
rng,
XLOOKUP(
_seq,
A2:A6,
rng,
,
-1
)
),
SUMPRODUCT(_e1(C2:C6),
_e1(D2:D6))
)
)
)Excel solution 12 for Date Range Spend Total, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(z;
LET(
f;
MIN(
A2:B6
);
f+SEQUENCE(
;
MAX(
A2:B6
)-f+1
)-1
);
MAP(F2:F4;
G2:G4;
LAMBDA(p;
o;
SUM(VALUE(FILTER(MAP(
z;
LAMBDA(
e;
TEXTJOIN(
;
;
MAP(
A2:A6;
B2:B6;
C2:C6;
D2:D6;
LAMBDA(
a;
b;
c;
d;
IF(
AND(
e>=a;
e<=b
);
c*d;
""
)
)
)
)
)
);
((z>=p)*((z<=o)))))))))Excel solution 13 for Date Range Spend Total, proposed by Julien Lacaze:
=LET(bf,
$A$2:$A$6,
bt,
$B$2:$B$6,
cost,
$C$2:$C$6*$D$2:$D$6,
MAP(F2:F4,
G2:G4,
LAMBDA(a,
b,
SUM(((bf<=a)*(a<=bt)*(bf<=b)*(b<=bt)*(1+b-a)+(bf<=a)*(a<=bt)*(b>bt)*(1+bt-a)+(bf<=b)*(b<=bt)*(1+b-bf)*(aExcel solution 14 for Date Range Spend Total, proposed by Pieter de Bruijn:
=LET(a,
A2:A6,
b,
B2:B6,
d,
C2:C6*D2:D6,
f,
F2:F4,
g,
G2:G4,
h,
MAKEARRAY(ROWS(
f
),
ROWS(
a
),
LAMBDA(r,
c,
LET(x,
INDEX(
f,
r
),
y,
INDEX(
a,
c
),
SUM(--((SEQUENCE(
1,
1+INDEX(
g,
r
)-x,
x
))=(SEQUENCE(
1+INDEX(
b,
c
)-y,
,
y
))))))),
BYROW(
h,
LAMBDA(
z,
MMULT(
z,
d
)
)
))Excel solution 15 for Date Range Spend Total, proposed by Nicolas Micot:
=LET(tabFiltre;
FILTRE($A$2:$D$6;
($A$2:$A$6<=G2)*($B$2:$B$6>=F2));
SOMME(BYROW(tabFiltre;
LAMBDA(a;
INDEX(
a;
1;
3
)*INDEX(
a;
1;
4
)*(MIN(
G2;
INDEX(
a;
1;
2
)
)-MAX(
F2;
INDEX(
a;
1;
1
)
)+1)))))Excel solution 16 for Date Range Spend Total, proposed by Hussain Ali Nasser:
=MAP(
F2:F4,
G2:G4,
LAMBDA(
_from,
_to,
SUM(
XLOOKUP(
SEQUENCE(
_to-_from+1,
,
_from
),
A2:A6,
C2:C6*D2:D6,
,
-1
)
)
)
)Solving the challenge of Date Range Spend Total with Python in Excel
&&&