CODEWITHSUNDEEP
google-sheets
G-J
G-J

Reputation: 1068

Google Sheets Auto Repeat Using arrayformula()

Based on this sample data, I have this formula which gives me the accurate results with one major problem... It does not auto-populate to new rows

=arrayformula(if(countif(filter(G$2:G,A$2:A=A2,B$2:B=B2),">"&G2)>0,countif(filter(G$2:G,A$2:A=A2,B$2:B=B2),">"&G2),COUNTIF(filter(E$2:E,A$2:A=A2,B$2:B=B2),"Finished")))

I have tried this formula to see if it will auto-populate to the new rows...

=arrayformula(IF(ISNA(A2:A),,if(countif(filter(G$2:G,A$2:A=A2,B$2:B=B2),">"&G2)>0,countif(filter(G$2:G,A$2:A=A2,B$2:B=B2),">"&G2),COUNTIF(filter(E$2:E,A$2:A=A2,B$2:B=B2),"Finished"))))

...the above formula does auto-populate; however, every value is 1 instead of the correct value.

I tried a simple formula which does not do everything the above does but might help troubleshoot. I was under the suspicion that the above formula was only displaying results of the first row over and over. To test I tried this formula...

=arrayformula(IF(ISNA(A2:A),,indirect("g"&ROW(indirect("g2:g"&counta(G2:G))))))

...it turns out that the above formula does display the results from G2 into each row. If I could figure out the reason why, I am sure I could take the concept from the solution to this simple formula and add it to the above more complex one.

Upvotes: 3

Views: 753

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Please, try this formula:

=ArrayFormula(if(
 mmult(
  --(A2:A=TRANSPOSE(A2:A))*
  --(B2:B=TRANSPOSE(B2:B))*
  --(G2:G<TRANSPOSE(G2:G)),
  row(A2:A)^0)>0,
 mmult(
  --(A2:A=TRANSPOSE(A2:A))*
  --(B2:B=TRANSPOSE(B2:B))*
  --(G2:G<TRANSPOSE(G2:G)),
  row(A2:A)^0),
 mmult(
  --(A2:A=TRANSPOSE(A2:A))*
  --(B2:B=TRANSPOSE(B2:B)),
  --(E2:E="Finished"))
 )
)

Caution! It works slow, so it's better to delete blank rows in the worksheet. Even better to use this formula in 2 steps. Step 1 formula: 

=ArrayFormula(mmult(--(A2:A=TRANSPOSE(A2:A))*
--(B2:B=TRANSPOSE(B2:B))*
--(G2:G<TRANSPOSE(G2:G)),
row(A2:A)^0))

And step 2 formula:

=ArrayFormula(mmult(--(A2:A=TRANSPOSE(A2:A))
*--(B2:B=TRANSPOSE(B2:B)),
--(E2:E="Finished")))

Open ranges overload this formula. It also could work faster if you use:

  • offset(E2,,,counta(E2:E)) instead of E2:E

Upvotes: 2

Related Questions