Dynamic array in sumproduct function

[FM89]

hi all,

Trying to set up a sumproduct of two columns whereas one column needs to retrieve its data from another table. Below a simplified example whereas I'm looking for a solution for the green numbers:

The total in sheet 1 calculates the total EUR amount based on the conversion rates in sheet 2. Sheet 1 is in reality however significant with continuously lines being added, removed and changed. Also new currencies are regularly added to sheet 2. Looking for a formula for the green totals in sheet 1 which does not need alternation when changes are made or lines are added to one of the two sheets.

Thanks.

 

[Dave Patton]

You could use Lookup, XLookup, or other alternatives.

T202112a.xlsm
	A	B	C	D
1	Total	59.4	61	59.2
2	EUR	10	10	10
3	EUR	10	10	10
4	USD	10	10	10
5	USD	10	10	10
6	GBP	10	10	10
7	GBP	10	10	10
1a

Cell Formulas
Range	Formula
B1:D1	B1	=SUMPRODUCT(B2:B7,LOOKUP($A$2:$A$7,Sheet2!$A$2:$A$4,Sheet2!B2:B4))

T202112a.xlsm
	A	B	C	D
1
2	EUR	1	1	1
3	GBP	1.12	1.18	1.14
4	USD	0.85	0.87	0.82
Sheet2

 

[Peter_SSs]

Welcome to the MrExcel board!

Adding to Dave's suggestion, I would choose XLOOKUP as a more efficient function and convert the data in the two sheets to formal tables if they are not already. That way you will get the automatic expansion/contraction of the relevant ranges in the formulas as you had requested.

Below, the table in Sheet2 I have named 'tblLookup' and the table in Sheet1 'tblData'

FM89.xlsm
	A	B	C	D
1	Currency	Jan	Feb	Mar
2	EUR	1	1	1
3	USD	0.85	0.87	0.82
4	GBP	1.12	1.18	1.14
Sheet2

XL2BB shows 3 separate formulas below. You cannot drag the B1 formula right across row 1, but you can drag it from B1 to C1 then drag C1 to D1 (etc if you have more columns). That is a slight inconvenience but it only has to be done once.

FM89.xlsm
	A	B	C	D	E
1	Total	59.4	61.0	59.2
2	Currency	Jan	Feb	Mar
3	EUR	10	10	10
4	EUR	10	10	10
5	USD	10	10	10
6	USD	10	10	10
7	GBP	10	10	10
8	GBP	10	10	10
9
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(tblData[Jan],XLOOKUP(tblData[[Currency]:[Currency]],tblLookup[[Currency]:[Currency]],tblLookup[Jan],0))
C1	C1	=SUMPRODUCT(tblData[Feb],XLOOKUP(tblData[[Currency]:[Currency]],tblLookup[[Currency]:[Currency]],tblLookup[Feb],0))
D1	D1	=SUMPRODUCT(tblData[Mar],XLOOKUP(tblData[[Currency]:[Currency]],tblLookup[[Currency]:[Currency]],tblLookup[Mar],0))

 

[FM89]

Thanks both. The lookup doesn't seem to work, it does not always matches the right currencies. xlookup works perfect, didn't knew this could be used on a entire column.

 

[Dave Patton]

"The lookup doesn't seem to work, it does not always matches the right currencies."

Check the help information for Lookup.
The Lookup works. Is your table or array sorted ascending?

 

[Peter_SSs]

You're welcome. Thanks for the follow up.