% of an item in a column (pivot table)

[raphael.goncalves]

I have 3 columns in my database, column a) in which an item can be "A" or "B", column b) a column with a customer name and column c) a column with the value.

In the pivot table I want to show for each customer the sum of column c), and the % of the item "A" from the sum in column c).

Let's say "a" it's my product, so I want to show from the total sales of each customer, how much is "a", but I still want to show how much are my total sales without dividing "A" and "B".

Example:
Total Sales Percentage of "A"
Customer John 10 100%
Customer James 15 80%
Customer Jack 12 17%

It means that customer John bought 10 pieces, all of them are "A", James bought 15 pcs, 12 of which are "A", and Jack bought only 2 "A"s.

Anyone has any idea how to do it?

 

[Andrew Poulsom]

Welcome to MrExcel.

Add a column to your source data with a formula like:

=SUMPRODUCT(--(A$2:A$10="A"),--(B$2:B$10=B2),C$2:C$10)/SUMIF(B$2:B$10,B2,C$2:C$10)

Expand the ranges to suit, but make sure that they all have the same number of rows and avoid using entire columns.

Include Average of that column in your pivot table's data area.

 

[raphael.goncalves]

Thanks a lot. Just a simple question, what should I avoid using entire columns in the formulas?

 

[Andrew Poulsom]

The SUMPRODUCT function doesn't allow entire columns prior to Excel 2007.

 

[raphael.goncalves]

Haven't noticed before. Thanks!