SUMPRODUCT of two arrays but not necessarily in the same order (Index/match/IFs?)

[magemaester]

Hi guys,

I want to obtain the sumproduct of the following two arrays, essentially 1 x 0.2 + 2 x 0.1 + 3 x 0.3.

A 1
B 2
C 3

B 0.1
A 0.2
C 0.3

SUMPRODUCT would work neatly if the first column in both these arrays were identically ordered (i.e. ABC and ABC). But because they're not so, it doesn't work. The actual spreadsheet I'm manipulating has hundreds of rows where the two arrays are not identically ordered. I've tried searching for solutions on similar topics but I haven't been able to find a solution thus far. Is anyone able to help? I have a feeling that it isn't as simple as it looks and it might required nested IFs and index matches.

Many thanks!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Code​

[/td][td]

Value​

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

Code​

[/td][td]

Value​

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

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

1​

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

B​

[/td][td]

0,1​

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

1,3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

B​

[/td][td]

2​

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

A​

[/td][td]

0,2​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

C​

[/td][td]

3​

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

C​

[/td][td]

0,3​

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

Formula in G2
=SUMPRODUCT(B2:B4,SUMIF(D2:D4,A2:A4,E2:E4))

Hope this helps

M.

 

[magemaester]

that works perfectly! Thank you! I didn't know SUMIFs can be used to re-arrange an array

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.