I would like to sum row values based on column headers condition, like on the screenshot, but I would like to have an automatically extended arrayformula so the formula is only in G1 and H1.

CodePudding user response：

use:

={BYROW(FILTER(A2:E10, A1:E1="A"), LAMBDA(xx, SUM(xx))), 
  BYROW(FILTER(A2:E10, A1:E1="B"), LAMBDA(xx, SUM(xx)))}

CodePudding user response：

TRANSPOSE the array and use QUERY to group by the first Col and sum the remaining columns.

=TRANSPOSE(
  QUERY(
    QUERY(
      TRANSPOSE(A1:E5),
      "select Col1,sum(Col"&JOIN("),sum(Col",SEQUENCE(ROWS(A1:E5)-1,1,2))&") group by Col1",
      0
    ),
  "offset 1",0)
)

Advantages:

• QUERY is faster
• Supports unlimited number of headers, not just A and B

CodePudding user response：

Use this formula to eliminate 0's after the last value.

=ArrayFormula({ 
 IF(IF(ROW(A2:E)<=MAX(IF(A2:E="",,ROW(A2:E))),1,0)<>1,,
    BYROW($A$2:$E, LAMBDA(rg, SUMIF(IFERROR(REGEXMATCH($A$1:$E$1, REGEXEXTRACT(G1, ".")),""), true, rg)))),
 IF(IF(ROW(A2:E)<=MAX(IF(A2:E="",,ROW(A2:E))),1,0)<>1,,
    BYROW($A$2:$E, LAMBDA(rg, SUMIF(IFERROR(REGEXMATCH($A$1:$E$1, REGEXEXTRACT(H1, ".")),""), true, rg))))})

Short version without spilling 0's

=ArrayFormula(LAMBDA(r,h, { 
 IF(IF(ROW(r)<=MAX(IF(r="",,ROW(r))),1,0)<>1,,
    BYROW(r, LAMBDA(r, SUMIF(REGEXMATCH(h, "A"), true, r)))),
 IF(IF(ROW(r)<=MAX(IF(r="",,ROW(r))),1,0)<>1,,
    BYROW(r, LAMBDA(r, SUMIF(REGEXMATCH(h, "B"), true, r))))})(A2:E,A1:E1))