# SUMIF column by comparing two date columns

``````=ARRAYFORMULA(if(A3:A\$900="";"";IF(DATEVALUE(History!C\$3:C\$900)<D3:D900;SUMIF(History!A\$3:A\$900;A3:A900;C\$3:C\$900)*SUMIF(History!A\$3:A\$900;A3:A900;History!B\$3:\$B900);"")))
``````

I want to SUM `C3:C900` if the dates in `History!C3:C900` are less than the dates in `Data!D3:D900`

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

1. LET was used simply to avoid retyping the range `History!A:C` instead assigning it the name `h`
2. INDEX is used to return columns in `h` by their position
3. 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`
4. 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)))))
`````` 