How to sum multiple columns using a single formula in Google Sheets?-CodePudding

The data that i have is in a 2D fashion

I want the output in this type of schema using a Single Formula!

I've tried PIVOT and SUMIF,SUMIFS,QUERY functions. But, Alas unable to figure out the same. Can anyone please help me with this

CodePudding user response：

You can use SUMPRODUCT for this task.

Formula B12 dragged right and down:

=SUMPRODUCT($B$5:$I$8*($A$5:$A$8=$A12)*($B$3:$I$3=B$11))

Result:

Pay attention at all those $ when adapting to your ranges.

CodePudding user response：

you can also try this single formula to create the summary table.

=LAMBDA(x,y,{{"",y};x,MAKEARRAY(COUNTA(x),COUNTA(y),LAMBDA(r,c,SUM(FILTER(FILTER(B3:I,B1:I1=INDEX(y,,c)),A3:A=INDEX(x,r)))))})(FILTER(A3:A,A3:A<>""),UNIQUE(FILTER(B1:I1,B1:I1<>""),1))