CODEWITHSUNDEEP
excelexcel-formulasumproduct
paul
paul

Reputation: 69

Sum product of two table by column matching

I have two tables, first with currency and value and the second one with currency and x-rate. by matching currency columns from two tables, I want to multiply and sum the values.

Currency value
EUR 10
GBP 20
CHF 30
EUR 40
GBP 50

And the second one

Currency x_rate
EUR 1
GBP 1.2
CHF 1.3

The result should be: 10*1 + 40*1 + 20*1.2 + 50*1.2 + 30*1.3 = 173

Upvotes: 0

Views: 417

Answers (2)

P.b
P.b

Reputation: 11438

=SUMPRODUCT(($B$2:$B$6)*INDEX($E$2:$E$4,MATCH($A$2:$A$6,$D$2:$D$4,0)))

Where $A$2:$B$6 is the first table and $D$2:$E$4 is the second table.enter image description here

Upvotes: 1

basic
basic

Reputation: 11968

Use array formula:

=SUMPRODUCT(B2:B6*TRANSPOSE(E2:E4)*(A2:A6=TRANSPOSE(D2:D4)))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Upvotes: 2

Related Questions