Sub Totals in Pivot Tables

monkeyspank

Board Regular
Joined
Feb 12, 2014
Messages
70
I have a series of items that have quantities. Is there away to provide me a count of just the items themselves? For example,



Item Quantity

XXX 5
VVV 7
HHH 1
XXX 5
XXX 10

Well I want to know the number of times ITEM XXX was ordered and the total quantity that was ordered. Can that be done in Pivot Tables?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes - put Item in the Row field area, then also into the Value field area. Then add Quantity to the Value area too.
 

monkeyspank

Board Regular
Joined
Feb 12, 2014
Messages
70
What if I know I have order this item three times but I only want to count if the item has ever been ordered? So, I am grouping the total quantity for ITEM XXX which in this case is 20 and I can the item # once. Hope this makes sense.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Only if you have Excel 2013 or PowerPivot. There is no DistinctCount function in regular pivot tables. However you could use a custom number format to display 1 in that field for any non-zero value.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,513
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you add your data to the Data Model when creating the pivot table (it can't be an .xls format workbook) then you should have the DistincCount option available to you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,172
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top