Multiply & sum of numbers and the equivalents of datas (Array sum in array)

Special-K99 said:

What result are you expecting from this?

A*3,13

Click to expand...

Special-K99 said:

Why have you put ,13 ,62 ,12 after each number?
Is this a currency?

Click to expand...

McExcel said:

Hi everyone! First post ! Sorry for the title :D My English is not excellent so I show my problem on an example:

A	3,13		equivalents
C	2,62		A	1
C	4,12		B	2
F	10,13		C	3
D	83,2		D	4
			E	5
			F	6
	Question: How can multiply Equivalents of A,B,C... in one step?
	A*3,13 + C*2,62 + C*4,12 + F*10,13 + D*83,2

<TBODY>
</TBODY>

The answer should be:120,2 sorry 416,93


I've tried vlookup and with array formulas using (control+shift+enter). But I couldn't solve.

For example:

={SUM(VLOOKUP(A1:A5,equivalents,2,false),B1:B5)} .... not working

Click to expand...

Aladin Akyurek said:

The data is located in A:B, equivalencies in D:E...

Try rather:

=SUMPRODUCT(SUMIF($D$2:$D$7,$A$1:$A$5,$E$2:$E$7)*$B$1:$B$5)

Click to expand...

billszysz said:

=SUM(LOOKUP($A$1:$A$5,$D$2:$E$7)*$B$1:$B$5)
Your data in D2:E7 has to be sorted by D column

CSE formula

Regards

Click to expand...

billszysz said:

More exotic solution...

=SUM(INDEX($E$2:$E$7,N(CHOOSE(1,MATCH($A$1:$A$5,$D$2:$D$7,0))))*B1:B5)

CSE formula

Click to expand...

McExcel said:

Omg :D Thanks guys all of them works perfectly! They are very helpful for giving point of view.

Click to expand...

Thank you dear Aladin Akyurek. I've get understand quickly your solution.

Click to expand...

Dear billszysz thanks for your effort. I'd read some index-match solutions but never face lookup solution. Someone says to someone you can't use lookup functions with CSE.

Click to expand...

Update: Note for other users. I've checked again. Yes, sum-Lookup solution is working only when datas are sorted, other two solution works in any case.

Click to expand...