[Pivot Table] Counting multiple column

eyz4eva

New Member
Joined
Aug 10, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

 

Attachments

  • Screenshot_20210810_232502.jpg
    Screenshot_20210810_232502.jpg
    82 KB · Views: 5

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

eyz4eva

New Member
Joined
Aug 10, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Ok, after going through the forum, I was able to get the tabulated qty by using sumif(product) + sumif(products) + sumif(Product3) ........
 

eyz4eva

New Member
Joined
Aug 10, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,818
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 10, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,818
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 10, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,818
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can produce your current data from the revised layout, so that shouldn’t be a problem.
 

Forum statistics

Threads
1,148,189
Messages
5,745,238
Members
423,936
Latest member
Conservatopia

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top