Need a way to dynamically sum multiple columns into a Merged cell of varying heights-CodePudding

In this scenario, I am looking to total Columns G through M by 'user' in Column E. Column N does have the correct data, but it is using the formula =sum(f4:m5). I am looking for a way to add an array formula because the heights of the merged cells in column N varies often. A formula would be preferred to a script in this case.

CodePudding user response：

try:

=INDEX(IFNA(VLOOKUP(F2:F, 
 QUERY(SPLIT(FLATTEN(IF(G2:M="",,F2:F&"♀"&G2:M)), "♀"), 
 "select Col1,sum(Col2) group by Col1"), 2, 0)))