SOLVED

Pricing of components costs as part of a bundle

%3CLINGO-SUB%20id%3D%22lingo-sub-3322095%22%20slang%3D%22en-US%22%3EPricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322095%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20hoping%20my%20subject%20makes%20a%20bit%20of%20sense%20but%20here%20goes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20find%20a%20way%2Fformula%20that%20can%20help%20me%20group%20the%20costs%20of%20each%20individual%20item%20that's%20part%20of%20a%20bundle.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20all%20the%20data%20such%20as%20bundle%20codes%2C%20individual%20product%20codes%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20bundle%20codes%20are%20duplicated%20depending%20on%20the%20number%20of%20components%20in%20said%20bundle.%20For%20example%2C%20a%20bundle%20has%203%20items%2C%20the%20data%20range%20duplicates%20the%20bundle%20code%203%20times%20for%20each%20individual%20product.%20Not%20sure%20if%20this%20info%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3322095%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3322564%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Hans%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20getting%20back%20to%20me.%20Here%20is%20the%20Dropbox%20link%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fscl%2Ffi%2Foyfy84ld6duipiltmptgv%2FSample-data.xlsx%3Fdl%3D0%26amp%3Brlkey%3Dtsfwz79h626xd5eoej0uxh3t8%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fscl%2Ffi%2Foyfy84ld6duipiltmptgv%2FSample-data.xlsx%3Fdl%3D0%26amp%3Brlkey%3Dtsfwz79h626xd5eoej0uxh3t8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3323228%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3323228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1381795%22%20target%3D%22_blank%22%3E%40Pro-Pix%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20I%20have%20downloaded%20the%20workbook.%3C%2FP%3E%0A%3CP%3ECan%20you%20explain%20what%20you%20want%20to%20do%20-%20perhaps%20with%20an%20example%20of%20the%20expected%20output%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3322126%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1381795%22%20target%3D%22_blank%22%3E%40Pro-Pix%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3323901%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3323901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1381795%22%20target%3D%22_blank%22%3E%40Pro-Pix%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat'suseful.%20In%20H2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A2%3DA1%2C%22%22%2CSUMPRODUCT((%24A%242%3A%24A%2420%3DA2)*%24F%242%3A%24F%2420%2C%24G%242%3A%24G%2420))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3323822%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3323822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20that%20would%20help!%3CBR%20%2F%3ESo%20basically%2C%20I%20need%20to%20know%20if%20there's%20a%20way%20or%20formula%20I%20can%20put%20in%20column%20H%20that%20compares%20the%20references%20in%20column%20A%20or%20B%20and%20totals%20everything%20in%20column%20F%20as%20long%20as%20the%20references%20in%20A%20or%20B%20are%20the%20same.%20Hopefully%20that%20makes%20sense%3F%3CBR%20%2F%3EThe%20end%20result%20I'm%20aiming%20for%20is%20to%20find%20out%20the%20profit%20margin%20of%20a%20bundle%2C%20taking%20into%20account%20the%20cost%20prices%20of%20each%20individual%20item%20in%20the%20bundle.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ProPix_0-1651759483243.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369477i604D85821897B839%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ProPix_0-1651759483243.png%22%20alt%3D%22ProPix_0-1651759483243.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESomething%20that%20looks%20like%20this%20at%20the%20end%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3324062%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20of%20components%20costs%20as%20part%20of%20a%20bundle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3324062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow%20so%20quick!%20Thank%20you%20very%20much!%20It's%20much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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!