1

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

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.