SUMIF column by comparing two date columns

Question

Please help with this formula.

=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

https://docs.google.com/spreadsheets/d/17C9FDyXHnFLk5z706bBI6iHjHleq21LZm4YCagsOuCY/edit?usp=drivesdk

Answer 1

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)))))