0

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.

Improve this question
0

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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?
    – Luuk Hijne
    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

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