Multiple Values for a cell

[allnet000]

I am working on a spreadsheet that has three columns.

A - Product (stays the same no changes)
B - Amount Ordered (varies)
C - Purchased By (varies)

My goal is to set up a formula that shows a summary of how much a person has ordered. Sounds straight forward, but the problem I am running into is when 2 or more people are making a combined purchase. In those cases the cost will be divided equally between the buyers. Is there a way to achieve this goal?

I included a sample of the spreadsheet. Typically this is about a 300-400 line report.

Thank you.

[modytrane]

It's not very clear what you are looking for.
Please post a sample worksheet with some examples.
What happens if two people order the same item, but in different quantity?
What about three people ordering the same item? There can be so many different scenerios, you really need to define in specific terms what the conditions are and what the outcome is for each condition.
The programs and formulas can't think for themselves, so you have to cover all the bases and intstruct the program to follow the rules.
modytrane.

[allnet000]

a sample should be attached now.

If multiple people order the same product or have different quantities, they will be listed on their own line. What I am trying to solve is when two or more people split the cost of the products they bought. The only total I care about is the amount spent. I need to make sure that nobody is going over their budget. Thank you.

[modytrane]

see the attached example.
not very clever, but it does the job.
someone else may come up with clever formulas to do the job, in the meantime use this.
you can hide the intermediate values and tables to make it look neat.
I think VBA macro can do this easily, if you are interested, post a request.
modytrane

[allnet000]

Thank you very much. That works for me.

[ChemistB]

Without helper columns in B15

=SUMPRODUCT(--(ISNUMBER(FIND($A15,$C$6:$C$10))),$B$6:$B$10/(LEN($C$6:$C$10)+1-LEN(SUBSTITUTE($C$6:$C$10,",",""))))

[modytrane]

I knew someone would come up with a formula.
Clever use of Find and Substitute functions, CB.
modytrane