SUMIFS with dynamic range

[worldCurrencies]

I have time series data. In Column A is the date (monthly) in ascending order, in column B is a boolean value, and column C are values. and D1 = 36. I want to sum everywhere where B = 1 for the past 36 months. I have been tinkering around a bit but cant seem to find the correct formula.

 

[Cubist]

Try:

=SUM(TAKE(B2:B52*C2:C52,-D1))

 

[Vogateer]

Please change the ranges accordingly:

=LET(amounts,C2:C51, boolean_range, B2:B51, date_range, A2:A51, starting_date, MAX(date_range)-DATE(0,D1,0), SUMIFS(amounts, date_range, ">="& starting_date, boolean_range, 1))

 

[Vogateer]

Nice one, Cubist! I'll have to remember that trick.