Trying to apply a SUMIF function over a set range using an arrayformula

Question

Google spreadsheet example

I am trying to combine an Arrayformula with a SUMIF statement so that these calculations will keep updating as new rows are added or taken away.

Here is what I'm trying to do: Row 2 contains a header of either a drawing number or Total for whatever unit is shown in row 1. I need to sum the total for each individual part in column A for all the different units for the project total.

For example, using row 3 in the linked sheet. When a cell in row2 = TOTAL, sum the values the corresponding column and row 3. So that would be C3, G3, J3, N3, and S3.

Then the arrayformula should cause the same calculation to happen for the remaining specified number of rows, using a namedrange that updates to include as many cells for when column A is not empty, so in this case it would be A3:A243.

Formula would need to be input once and work no matter how many rows or columns are added.

I have tried a couple of things so far, but they run into the same issues every time.

Formula below outputs the same number, sum for the row the formula is input, for the entire array. =ArrayFormula(if(A2:A243<>"",sumif($2:$2,"TOTAL",3:3),""))

This formula has the same issue =ArrayFormula(if(A2:A243<>"",sumif($2:$2,"TOTAL",indirect(""&row()&":"&row())),""))

Formula below only outs data for the row it is currently in

=ArrayFormula(sumif($2:$2,"TOTAL",3:243))

Can't figure out why it isn't summing values from the next row.

Answer 1

You may try:

=byrow(C3:U,lambda(Σ,if(counta(Σ)=0,,sumif(C2:2,"Total",Σ))))