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

DATA	A	B		SUMMARY	A	B
	I	II	III	IV	V	I	II	III	IV	V		X	11	2
X	2		2			1			1	1		Y	1	2
X	9		3	5								Z	2	0
Y		1				2	1		2
Z	2		5	5				7	3	4
Z			7	5						8
Y	1	2		5	6			8	3
X			4	4		1				4

<tbody>
</tbody><colgroup><col><col span="10"><col><col><col span="2"></colgroup>

 

[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?