For this task, either the QUERY or FILTER functions together with SUM will work best.
In both cases, the MAP function enables passing each pair of lookup values, "Ticker Symbol" and "ExDate Dividend", into a LAMBDA function mapped to the variables t
for ticker, and d
for date. The LAMBDA function then applies its formula once for each set of values.
QUERY Formula
The LAMBDA's formula uses QUERY to SUM History!B:B
if both
- the symbol in
History!A:A
matches t
, AND
- the date in
History!C:C
is prior to d
=MAP(A3:A; D3:D; LAMBDA(t; d; IF(t<>"";
QUERY(History!A:C; "select sum(B)
where A matches '"&t&"' AND C < date '"&TEXT(d;"yyyy-mm-dd")&"'
Label sum(B) ''");
IFERROR(1/0))))

FILTER Formula
- LET was used simply to avoid retyping the range
History!A:C
instead assigning it the name h
- INDEX is used to return columns in
h
by their position
- The LAMBDA function's formula applies a FILTER to
History!B:B
/ INDEX(h;;2)
where both
- the ticker in
History!A:A
matches t
, AND
- the date in
History!C:C
is prior to d
- The SUM function is applied to the filtered values.
=LET(h; History!A:C;
MAP(A3:A; D3:D; LAMBDA(t; d;
IF(t<>"";
SUM(FILTER(INDEX(h;;2); INDEX(h;;1)=t; INDEX(h;;3)<d));
IFERROR(1/0)))))
