Vertical and Horizontal Criteria Summation

[brinks19]

Hello All,

I need some help with the following:

I have a list of codes in column A in which some are repeated. Then I have specific codings across the top horizontally that are individual, but will not always be in the same order. I need to be able to pull the sum by matching both. I'll provide an image below of what I would like to be able to do. In this example I would want "30" to populate. Any help is much, much appreciated. Thanks!

[TABLE="width: 327"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Horizontal[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Vertical[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Criteria[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Vertical[/TD]
[TD]Horizontal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Here's one way:

Book1
	A	B	C	D	E
1		Horizontal	1	2	3
2	Vertical
3	1		10	15	20
4	2		15	25	35
5	3		20	35	10
6	4		25	10	10
7	1		30	15	5
8	3		35	20	10
9	2		40	10	5
10
11	Criteria
12	Vertical	Horizontal
13	1	2	30
Sheet21

Cell Formulas
Range	Formula
C13	=SUMPRODUCT((A3:A9=A13)*(C1:E1=B13)*C3:E9)

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Horizontal[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Vertical[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

10​

[/TD]
[TD]

15​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

15​

[/TD]
[TD]

25​

[/TD]
[TD]

35​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD]

20​

[/TD]
[TD]

35​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

25​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD]

30​

[/TD]
[TD]

15​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

35​

[/TD]
[TD]

20​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2​

[/TD]
[TD]

40​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Criteria[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Vertical[/TD]
[TD]Horizontal[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
</tbody>[/TABLE]

D12=SUMPRODUCT((A3:A9=$A$12)*(B1:D1=$B$12)*($B$3:$D$9))

 

[brinks19]

Appears to work great. Thank you kindly!

 

[jtakw]

You're welcome.