Combine Multiple Products Into One Cell

[JoePong]

Hi!
I need a formula that sums the total cost for multiple products into one cell. Total cost = Quantity (from the left table) X Costs (from the middle table) for each product, and then adds all the products total cost together.
I've uploaded a file that shows the answer I'm looking for with sample data.
The left table shows products and their quantities. Products won't be repeated on this table.
The middle table shows all the costs for the product and products will show up multiple times here and will be unsorted.
I need the formula to be robust enough to work with hundreds of products.
Thanks in advance for any help!
Joe

 

[deciog]

JoePong

Perhaps this way, it confers

Decio

 

[pecoflyer]

I don't think you can solve this for hundreds of products without VBA or some other solution.
Formulas will not do

 

[rahulshewale1]

Hii @JoePong ,


try below formula,


=SUMPRODUCT(SUMIFS($G$3:$G$10,$F$3:$F$10,$A$3:$A$10),SUMIFS($B$3:$B$10,$A$3:$A$10,$A$3:$A$10))


Note : Press Ctrl+ Shift + Enter (This is Array formula)


Regard
Rahul shewale

 

[JoePong]

Thanks everyone for the help!

Rahul, this is exactly what I was looking for!

 

[bosco_yip]

JoePong

Perhaps this way, it confers

Decio

Hi Decio,

1] Your formula will give wrong result if "Product 2" Quantity/Cost C4/G4 changed to other values.

2] This is a question called "Sumifs total cost in difference size tables"

3] The Total Cost in J4, enter formula:

=SUMPRODUCT(SUMIFS(C3:C4,B3:B4,F3:F5),G3:G5)

4] Or, if you wanted to use Vlookup, in J5, enter formula :

=SUMPRODUCT(VLOOKUP(T(IF({1},F3:F5)),B3:C4,2,0),G3:G5)

Regards
Bosco

 

[deciog]

Bosco

I just have to learn from you, really this is wrong, the Portuguese translation showed something else.

translated, total products, then
+COUNTA($B$3:$B$4)

Many thanks for correcting

A Merry Christmas and a Happy New Year

Decio

 

[JoePong]

Hi Bosco, thanks for the reply.
Which of your solutions would be the least resource demanding?

 

[bosco_yip]

Hi Bosco, thanks for the reply.
Which of your solutions would be the least resource demanding?

Structure of this two formula in post #.06 is :

1] Formula [3] : SUMPRODUCT+SUMIFS

2] Formula [4] : SUMPRODUCT+VLOOKUP

Since both formula used Sumproduct function, only checking >>

Vlookup V Sumifs

It is the principle that lookup function always faster than mathematics function

Then,

SUMPRODUCT + VLOOKUP is the winner

Regards
Bosco

 

[JoePong]

Thank you!
This is very helpful!

 

[JoePong]

Bosco,
If you wouldn't mind, would you explain why the T function and array constant are necessary for the vlookup to work properly in this application?
I'm trying to figure it out but am coming up short.

 

[JoePong]

Found the answer in this link:

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/