sumifs with 2 sum ranges

[Salamullah]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Products[/TD]
[TD]Source[/TD]
[TD]

Actuals​

<strike></strike>
[/TD]
[TD]Plan[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]Loc[/TD]
[TD]40[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]Imp[/TD]
[TD]50[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]Imp[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]loc[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]
Actuals
Loc
a

Please share formula in cell A9 from the array A2:D5 with meeting condition in cell A6, A7 & A8.

In other words I am not able to find 2 sum ranges in SUMIFS formula

 

[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]Products[/TD]
[TD]Source[/TD]
[TD]Actuals[/TD]
[TD]Plan[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]a[/TD]
[TD]Loc[/TD]
[TD]

40​

[/TD]
[TD]

50​

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

3​

[/TD]
[TD]b[/TD]
[TD]Imp[/TD]
[TD]

50​

[/TD]
[TD]

30​

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

4​

[/TD]
[TD]c[/TD]
[TD]Imp[/TD]
[TD]

10​

[/TD]
[TD]

20​

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

5​

[/TD]
[TD]d[/TD]
[TD]loc[/TD]
[TD]

10​

[/TD]
[TD]

20​

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

6​

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

7​

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

40​

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

8​

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

9​

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



B7=SUMPRODUCT((B2:B5&A2:A5=A8&A9)*(C1:D1=A7)*(C2:D5))

 

[Salamullah]

Good Many thanks MARZIOTULLIO