google sheets - Why isn't the array formula applying to the entire column?

Question

I have a formula in cells F1 and G1 of this sheet. I want to keep the text that is in cells F/G8 and F9&G9 however it's giving me an error. Note that the formula is in an array so that it can apply retroactively to all of the cells in the column.

Here is the code used in Cell F1:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, QUERY('Raw Logs'!C2:F, "select C,sum(F) group by C label sum(F)''"), 4, 0)))

Here is the code used in Cell G1:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B, QUERY('Raw Logs'!C2:H, "select C,sum(H) group by C label sum(H)''"), 6, 0)))

Additionally, the data from the formula in Cell C1 is no longer populating (it was before) - the error says it can't because it's unable to overwrite C9 however there was text in C9 earlier and the data from the formula was still populating.

Here's the formula used in Cell C1:

=ArrayFormula(IFERROR(VLOOKUP(B:B,'Roster Registrations'!F1:G,2,FALSE)))

Any tips on how I can resolve this?

question from:https://stackoverflow.com/questions/65929900/why-isnt-the-array-formula-applying-to-the-entire-column

Answer 1

array formula needs a space to roll out - that's why. if you want to run it from that 1st row delete cells F8:F9 and use:

={"";"";"";"";"";"";"";"Total for February"; 
 "Activity"&CHAR(10)&"Hours"; ""; 
 ARRAYFORMULA(IFNA(VLOOKUP(B11:B&"", TEXT(QUERY('Raw Logs'!C2:F*1, 
 "select Col1,sum(Col4) 
  where Col1 is not null 
  group by Col1 
  label sum(Col4)''"), {"@", "[h]:mm:ss"}), 2, 0)))}

---

for G1 fx delete G9 and use this in G1:

={"";"";"";"";"";"";"";""; 
 "Number of"&CHAR(10)&"Tests"; ""; 
 ARRAYFORMULA(IFNA(VLOOKUP(B11:B, QUERY('Raw Logs'!C2:H*1, 
 "select Col1,sum(Col6) 
  where Col1 is not null 
    and Col1 <> 0
  group by Col1 
  label sum(Col6)''"), 2, 0)))}