• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine Multiple Products Into One Cell

J

JoePong

New Member
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
 

Attachments

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

Perhaps this way, it confers

Decio
Click to expand...
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
 

Attachments

Last edited:
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
 
Last edited:
JoePong said:
Hi Bosco, thanks for the reply.
Which of your solutions would be the least resource demanding?
Click to expand...

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
 
Last edited:
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.
 
You must log in or register to reply here.
Back
Top