[Pivot Table] Counting multiple column

[eyz4eva]

I made a simple excel sheet to store my order data.

The number of products is less than 10, but customers can sometimes order 4 to 5 different items with different quantity.

Name | Address | Contact | Product | Price | Qty | Sub-total | Product2 | Price2 | Qty2 | Sub-total2 | Product3 | Price3 | Qty3 | Sub-total3 | >>> Total

At the end of the day, I would like to tabulate the number of each products that had been sold, however, I was unable to generate a pivot table showing the quantity that each product had been sold.

When I tried to do a pivot table,

row - product, Product2, product3
value - qty, qty2, qty3

it gave me some weird table.

Sample.xlsx

Shared with Dropbox

www.dropbox.com

 

[eyz4eva]

Ok, after going through the forum, I was able to get the tabulated qty by using sumif(product) + sumif(products) + sumif(Product3) ........

 

[eyz4eva]

Any solution?

I realised that the function sumif is not a viable solution in future, as the no. of product list get longer.

a pivot table will be more convenient as I can omit products that are not selected.

 

[RoryA]

Can your sheet layout be changed? It is not ideal for reporting generally, and definitely not for a pivot table. Ideally you'd want just Name, Address, Product, Price and Quantity, with multiple rows for any customer that buys multiple products.

 

[eyz4eva]

Can your sheet layout be changed? It is not ideal for reporting generally, and definitely not for a pivot table. Ideally you'd want just Name, Address, Product, Price and Quantity, with multiple rows for any customer that buys multiple products.

yes, I can but change to what format?

can you guide me?

I'm a novice in excel.

 

[RoryA]

As I said, you'd just have columns for Name, Product, Price and quantity (I'd suggest storing name and address in a separate reference table rather than repeating the address for each row, unless you can have different addresses for a customer).

 

[eyz4eva]

As I said, you'd just have columns for Name, Product, Price and quantity (I'd suggest storing name and address in a separate reference table rather than repeating the address for each row, unless you can have different addresses for a customer).

ok, I got what you mean.

so if for example, a customer order 3 products, product A - 5, product B - 3, products C - 2

it will be something like

Name / Product A / 5 / 50
Name / Product B / 3 / 15
Name / Product C / 2 / 16

there will be a problem for me, because I am using the single row data to generate order label with name, address, products, qty and price in Microsoft word for printing.

I'm not sure how to resolve this.

 

[RoryA]

You can produce your current data from the revised layout, so that shouldn’t be a problem.