excelexcel-formulalambda

Formula to filter data based on prior 3 boolean "true" values

I am trying to use Excel to count the sum of the prior three entrances per each cell of the second row, "indexed" through the BOOL in the first row.

I was hoping to use offset over a filter but apparently it is impossible to use it.

Now I am thinking of using SUMIF and differences of partial sums, but I would like to find an easier way.

The output should be the 3rd row.

Thks

0 1 1 1 0 1 0 0 1 0 0 1 1 1 1 0
17 4 0 44 6 0 87 45 36 8 0 1 39 39 41 30
0 0 0 0 48 48 44 44 44 80 80 80 37 76 79 119
Solution

  • Not the best, but. Formula in B3 dragged right:

    =LET(a,TAKE(FILTER($A$2:A2,$A$1:A1=1),1,-3),IF(COUNTA(a)>=3,SUM(a),0))

    Result:

    enter image description here