SUM/Array Query using multiple criteria

[Bruiser]

Firstly, apologies for how the data appears on here

I am trying to calculate the total sum of X.Y or Z for all section A and B

For example A - X should = 29, Y = 15, Z = 24

The formula I have used only returns the sum for column I and not all columns I, II, III, IV, V. {=SUM(($B$3:$K$9)*($B$1:$K$1=N$1)*($A$3:$A$9=$M2))}

Any help would be greatly appreciated, and thanks in advance

[TABLE="width: 812"]
<tbody>[TR]
[TD]DATA[/TD]
[TD="colspan: 5"] A [/TD]
[TD="colspan: 5"] B[/TD]
[TD] [/TD]
[TD]SUMMARY [/TD]
[TD] A[/TD]
[TD] B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I[/TD]
[TD]II[/TD]
[TD]III[/TD]
[TD]IV[/TD]
[TD]V[/TD]
[TD] I[/TD]
[TD]II[/TD]
[TD]III[/TD]
[TD]IV[/TD]
[TD]V[/TD]
[TD][/TD]
[TD]X[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD] [/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Z[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="10"><col><col><col span="2"></colgroup>[/TABLE]

 

[Cross Terrier]

{=SUM((B3:B9+C3:C9+D3:D9+E3:E9+F3:F9)*(--(A3:A9=M3)))} works for A, and then you will need to change the column references for B

 

[Bruiser]

{=SUM((B3:B9+C3:C9+D3:D9+E3:E9+F3:F9)*(--(A3:A9=M3)))} works for A, and then you will need to change the column references for B

Thanks Cross Terrier, that's perfect. can I ask what the -- mean?

 

[Cross Terrier]

It turns TRUE, as the result of a formula, into 1 and FALSE into 0. This is used most for SUMPRODUCT formulae.

 

[Bruiser]

Thanks for your help and the explanation. Much appreciated.

 

[Bruiser]

Is there a way to make this dynamic so that I do not need to select each individual column?