0

This question already has an answer here:

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.

marked as duplicate by Rubén, serenesat, John C Sep 6 at 0:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

  • 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 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 at 6:47

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