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

[McExcel]

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

 

[billszysz]

Of the remaining two, I'd opt for an option that is faster and readily intelligible.

Aladin, if you look at my previous post, i used a word "exotic"

McExcel, You are welcome

Regards

 

[McExcel]

I really like the "exotic" solution like as Aladin's rat-run. I always test and try to understand how works.

Now, last and little request: How can we add if statement to the same problem?

For example

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

<tbody>
</tbody>

The answer should be 348.29

 

[Aladin Akyurek]

I really like the "exotic" solution like as Aladin's rat-run. I always test and try to understand how works.

but no need worrying the exotic...

Now, last and little request: How can we add if statement to the same problem?

...

=SUMPRODUCT(SUMIFS($E$2:$E$7,$D$2:$D$7,$A$1:$A$5,$C$2:$C$7,"AVAILABLE")*$B$1:$B$5)

 

[McExcel]

but no need worrying the exotic...



=SUMPRODUCT(SUMIFS($E$2:$E$7,$D$2:$D$7,$A$1:$A$5,$C$2:$C$7,"AVAILABLE")*$B$1:$B$5)

Dear Aladin

Works but the results were false. I need
A1*B1 if C1 has "AVAILABLE" + A2*B2 if C2 has "AVAILABLE" +... + so on...

By the way
A1 has "A" value and means 1
A2 has "C" value and means 3
A3 has "C" value and means 3
... so on

 

[Aladin Akyurek]

Dear Aladin

Works but the results were false. I need
A1*B1 if C1 has "AVAILABLE" + A2*B2 if C2 has "AVAILABLE" +... + so on...

By the way
A1 has "A" value and means 1
A2 has "C" value and means 3
A3 has "C" value and means 3
... so on

Is the expected result not 332.80? Please check this manually.

 

[McExcel]

Is the expected result not 332.80? Please check this manually.

Your formula gives the 332.80.

But I need like that:

1 * 3.13 = 3.13​

3 * 4.12 = 12.36​

4 * 83.2 = 332.80
​

+___________________

348.29
​

 

[Aladin Akyurek]

Your formula gives the 332.80.

But I need like that:

1 * 3.13 = 3.13​

3 * 4.12 = 12.36​

4 * 83.2 = 332.80
​

+___________________

348.29
​

Right.

Control+shift+enter, not just enter:

=SUM(SUMIF($D$2:$D$7,IF(C1:C5="available",$A$1:$A$5),$E$2:$E$7)*$B$1:$B$5)

 

[McExcel]

Right.

Control+shift+enter, not just enter:

=SUM(SUMIF($D$2:$D$7,IF(C1:C5="available",$A$1:$A$5),$E$2:$E$7)*$B$1:$B$5)

Dear Aladin,

I've used with a data (10k rows) and it works like a charm !

Today you're very very helpful. The solutions are not as very hard as I thought. I will examine all formulas after finishing my current job.

Also, I really need the fundamentals of array functions, then I should put the questions to experts. Thanks for your patience

 

[Aladin Akyurek]

Dear Aladin,

I've used with a data (10k rows) and it works like a charm !

Today you're very very helpful. The solutions are not as very hard as I thought. I will examine all formulas after finishing my current job.

Also, I really need the fundamentals of array functions, then I should put the questions to experts. Thanks for your patience

You are welcome. Thanks for providing feedback.

 

[billszysz]

Exotic formula....continued...hehehe

=SUM(IFERROR(INDEX($E$2:$E$7,N(CHOOSE(1,IF($C$1:$C$5="available",MATCH($A$1:$A$5,$D$2:$D$7,0),999999)))),0)*B1:B5)

It works, of course, but Aladin's formula is better

Regards