# Sumproduct within Arrayformula [duplicate]

This is my formula in cell `D2`:

`=SUMPRODUCT(B:B=B2;F:F>F2)+1`

The values in column are all of the same length, and are continually updated by contributions from google forms. Therefore a condition based on length makes no sense.

How can I make an Arrayformula of this, so that the `B2`'s and `F2`'s change with the row numbers? The function "indirect" isn't possible in an Arrayformula.

As far as I understood the problem, you just want to filter `B:B` and `F:F` then do a SUMPRODUCT.

So maybe this :

``````=SUMPRODUCT(ArrayFormula(IF(B:B=B2,B:B,0)),ARRAYFORMULA(if(F:F>F2,F:F,0)))+1
``````

How can I make an Arrayformula of this, so that the B2's and F2's change with the row numbers?

Try `INDIRECT` :

`B2` becomes `=INDIRECT("B"&ROW(B2))` | `Row(B2)` can be a formula to specifies which row you need.

• Thanks Paul for your answer. Unfortunately the function indirect didn't work for me inside the Arrayformula. Do you know an substitution for that as well? Sep 3, 2019 at 5:27
• Try to use indirect in a different cell ? Or maybe on a new tab, put your indirect formula in a1, first array in b:b etc.. It can be good practice to break down a formula in several part for maintenance, accessibility, lisibillity and debugging reasons.
– pjmg
Sep 3, 2019 at 6:47