find product of 2 cells and a match index in a row, then sum the result

dalexcel

New Member
I would like to find the Sum of the No. Units*price*relevant exchange rate according to month and currency.

Here is my two sets of data:
1. Info
 A B C D Date Currency No Unit Unit Price 1 05/Jan EUR 100 1.15 2 07 Jan USD 100 20 3 09 Feb HKD 200 100 4 10 Mar EUR 300 4 5 28 Mar USD 700 9

<tbody>
</tbody>

2. Exchange rate matrix (Range named FX)

 A B C D 1 Jan Feb Mar 2 EUR 1.1 1.2 1.3 3 HKD 0.13 0.14 0.15 4 USD 1 1 1

<tbody>
</tbody>

Essentially what I would like to find is one formula to Sum the products from column C, D, and the index match according to FX the range.

In other words

(C1*D1*Indexmatch to find exchange rate according to A1 and B2)+(C2*D2*Indexmatch to find exchange rate according to A2 and B2) etc......

I would like to avoid adding a column that finds the product for each row. Hence I'm asking for a formula to sum all info at once.

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
try this

Excel 2012
ABCDE
1DateCurrencyNo UnitUnit Price
205-JanEUR1001.15126.5
307-JanUSD100202000
409-FebHKD2001002800
510-MarEUR30041560
628-MarUSD70096300
7
8JanFebMar
9EUR1.11.21.3
10HKD0.130.140.15
11USD111
Sheet5
Cell Formulas
RangeFormula
E2=INDEX(\$B\$9:\$D\$11,MATCH(\$B2,\$A\$9:\$A\$11,0),MATCH(TEXT(\$A2,"mmm"),\$B\$8:\$D\$8,0))*C2*D2

Hi Dalexcel,

what you looking for gonna generate very long formula. adding 1 column certainly make it short. take a look at this.

change your month with number, 1, 2, 3, to make the formula work.

exchange rate will use index & match.
the total will use sumproduct.

but if you want to combine it all then the formula will be

D4*E4*INDEX(\$C\$16:\$E\$18,MATCH(C4,\$B\$16:\$B\$18,0),MATCH(MONTH(B4),\$C\$15:\$E\$15,0)) +
D5*E5*INDEX(\$C\$16:\$E\$18,MATCH(C5,\$B\$16:\$B\$18,0),MATCH(MONTH(B5),\$C\$15:\$E\$15,0)) +
.
.
.
.

[/IMG]

Replies
2
Views
392
Replies
3
Views
359
Replies
4
Views
131
Replies
3
Views
437
Legacy 143009
L
Replies
5
Views
362

1,217,476
Messages
6,136,883
Members
450,029
Latest member
MissQuotation

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back