Need help to sum range using both horizontal and vertical criteria

[JimB96]

I am trying to create a formula that will sum a range based on both horizontal and vertical criteria using the following data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20%[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]40%[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]40%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For example, if the horizontal criteria were "1" and the vertical criteria were "2", I need to sum the 20% and 40% amounts (60%). If the horizontal criteria were "2", and the vertical criteria were "3", I would need to sum the 25%, 25%, 50% (100%). I have tried using SUMIFS functions, but they do not appear to work with both horizontal and vertical axis criteria (I receive a #VALUE error).

Any advice on how this might work?

Thank you.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

Horizontal​

[/td][td]

Vertical​

[/td][td]

Sum​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]

20%​

[/td][td]

25%​

[/td][td]

​

[/td][td]

1​

[/td][td]

2​

[/td][td]

60%​

[/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]

40%​

[/td][td]

25%​

[/td][td]

​

[/td][td]

2​

[/td][td]

3​

[/td][td]

100%​

[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]

40%​

[/td][td]

50%​

[/td][td]

​

[/td][td]

2​

[/td][td]

1​

[/td][td]

25%​

[/td][/tr]
[tr][td]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

2​

[/td][td]

2​

[/td][td]

50%​

[/td][/tr]
[/table]


Formula in G2 copied down
=SUMPRODUCT($B$2:$C$4*($B$1:$C$1=E2)*($A$2:$A$4<=F2))

M.