how to sum values in a table with multiple criterias

0
votes

I need your help with an excel formula. I want to sum values of a table based on criterias in the first row and the first column

as shown in this image

e.g. row-criteria="z" and column-criteria="1": for those criterias the solution is 3.69 (1.06+1.38+1.25=3.69)
SUMPRODUCT does not work for me.

1
excelexcel-formulaexcel-tables
Can't see what columns those are, but: =SUMPRODUCT(($A$5:$A$9="z")*($B$4:$G$4=1)*$B$5:$G$9) should do the job. – Rory

1 Answers

0
votes

Rory's comment is optimal, though you may want step by step results. Then, you have to use additional columns (eg. AA-AC) to compute each intermediary result:

AA4: 1 AB4: 2 AC4: 3 AA5: =sum.if($B9:$G9,$4:$4)

AA6-AA9 and AB5-AC9: same formula extended from AA5

AA10: =sum(AA$5:AA$9)

AB10-AC10: same formula extended from AA10

Your end result wil be computed in AD10 =sum(AA10:AC10) as sums of cases of 1's, 2's and 3's.

Dollar symbols are used to have formula be extended and valid on nearby cells.

Where a single value is expected, $4:$4 is the same as <Current Column>$4. Same goes for columns.