CODEWITHSUNDEEP
excelheader
Haoheng Wang
Haoheng Wang

Reputation: 1

How to sum all cells based on each cell's row header (duplicated) and column header (duplicated) in Excel/Googlesheet?

In Excel/Googlesheet table where row & column headers have duplicated values, how can we sum up cells in range B2:F6 by looking at each cell's row & column headers, aiming a table screenshot

Upvotes: 0

Views: 438

Answers (1)

p._phidot_
p._phidot_

Reputation: 1952

Assuming the target table is located at A8:D11, with B8 = a, C8 = b, D8 = c and so on for x, y, z..

  1. generate helper sum table, based on a,b,c match criteria.. in H2, put =SUMIF($B$1:$F$1,B$8,$B2:$F2) and drag till J6 .
  2. get final sum from x ,y, z criteria.. in B9 put =SUMIF($A$2:$A$6,$A9,H$2:H$6) and drag till D11.

Done.

Hope it helps.

Upvotes: 0

Related Questions