Counting data for package mix anaylsis

[Angliojoe]

Hello Again

Hello All,
I have been having a lot of trouble with excel (Ive already posted on here today.

I have a question regarding counting data. I have a spread sheet as shown below, right now it only has Cart and package fields. My boss has asked me to produce a package mix chart so I was thinking if I can get the amount of each type of package on the same row I could then calculate percentages and do the chart which would be great

My major issue is that I have no idea how to do this. What kind of formula would I use in the columns that I’ve mocked up in the image?

Any help would be greatly appreciated,

Joe.

 

[obiron]

If you just have the Cart and Package columns (A & C) you can use a pivot table to give you exactly what you are looking for.

 

[Angliojoe]

Whats a pivot table? There is other data but this is the only relevant data to the cart and its contents, the other is just stock related.

Joe,

 

[Angliojoe]

Ive just checked and I dont think that a pivot data would help in this case. Basically I have a list of around a thousand carts and the packages they ordered and I need to be able to split the data so I find out out of all the thousand or so carts, what was the average mix of products.

If it was a number I would of thought I could use SUMIF in some way but due to it being text im lost.

 

[Angliojoe]

*Pivot table

 

[obiron]

If you have the two columns and create a pivot table from them you will get;

Carts down the rows, Package types across the top
Count of the intersections (you may need to change the aggregation type in the data range)

By default the pivot table will also include total columns.

the average product mix will be the ratio of basic to bargain to standard to ultimate.

e.g. if the totals are 2000,1000,1500,500 and there are 1000 carts then the average product mix will be 2 basic, 1 bargain, 1.5 standard and 0.5 Ultimate

Or am I missing something?

 

[Angliojoe]

Thanks, I managed to sort it in the end wit hthe pivot table Cheers guys