Home > Net >  How to sum rows with arrayformula based on what is in the column header?
How to sum rows with arrayformula based on what is in the column header?

Time:10-03

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.

How sum rows with arrayformula based on what is in the column header?

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)))}

enter image description here

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))))})

enter image description here

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))

enter image description here

  • Related