# Counting data for package mix anaylsis

#### Angliojoe

##### Board Regular
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.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.

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,

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.

*Pivot table

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?

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

Replies
1
Views
101
Replies
9
Views
264
Replies
3
Views
140
Replies
3
Views
132
Replies
4
Views
239

1,203,319
Messages
6,054,714
Members
444,745
Latest member
NickCourtney6

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back