SOLVED

Pricing of components costs as part of a bundle

Copper Contributor

Hi Everyone, 

 

I'm hoping my subject makes a bit of sense but here goes.

 

I want to find a way/formula that can help me group the costs of each individual item that's part of a bundle.

 

I have all the data such as bundle codes, individual product codes etc. 

 

The bundle codes are duplicated depending on the number of components in said bundle. For example, a bundle has 3 items, the data range duplicates the bundle code 3 times for each individual product. Not sure if this info helps.

 

Thanks in advance!

6 Replies

@Pro-Pix 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

@Hans Vogelaar 

 

Hi Hans, 

Thanks for getting back to me. Here is the Dropbox link

 

https://www.dropbox.com/scl/fi/oyfy84ld6duipiltmptgv/Sample-data.xlsx?dl=0&rlkey=tsfwz79h626xd5eoej0...

 

Thanks in advance!

@Pro-Pix 

Thank you. I have downloaded the workbook.

Can you explain what you want to do - perhaps with an example of the expected output?

@Hans Vogelaar 

Of course, that would help!
So basically, I need to know if there's a way or formula I can put in column H that compares the references in column A or B and totals everything in column F as long as the references in A or B are the same. Hopefully that makes sense?
The end result I'm aiming for is to find out the profit margin of a bundle, taking into account the cost prices of each individual item in the bundle.

ProPix_0-1651759483243.png

Something that looks like this at the end?

 

Thanks in advance!

best response confirmed by Pro-Pix (Copper Contributor)
Solution

@Pro-Pix 

That'suseful. In H2:

 

=IF(A2=A1,"",SUMPRODUCT(($A$2:$A$20=A2)*$F$2:$F$20,$G$2:$G$20))

 

Fill down.

@Hans Vogelaar 

Wow so quick! Thank you very much! It's much appreciated!

1 best response

Accepted Solutions
best response confirmed by Pro-Pix (Copper Contributor)
Solution

@Pro-Pix 

That'suseful. In H2:

 

=IF(A2=A1,"",SUMPRODUCT(($A$2:$A$20=A2)*$F$2:$F$20,$G$2:$G$20))

 

Fill down.

View solution in original post