Sub Totals in Pivot Tables

[monkeyspank]

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?

 

[RoryA]

Yes - put Item in the Row field area, then also into the Value field area. Then add Quantity to the Value area too.

 

[monkeyspank]

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]

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.

 

[monkeyspank]

I have Excel 2013. How would I do it? I didn't see an option. Is it an add-in?

 

[RoryA]

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.