0

I have the following Excel spreadsheet:

       A                  B              C 
                  Sent Quantity      Returned Products
1  Product A             500              0
2  Product A             400            300
3  Product A             600            400
4  Product B             250              0
5  Product B             300            150
6  Product C             700              0

The table shows the sent quantity (Column B) and returned quantity (Column C) of each sale (order) of a product (Column A).

I created a simply PivotTable based on this data which gives me the following result:

            Count of Sent Quantity       Count of Returned Quantity
Product A          3                             3
Product B          2                             2
Product C          1                             1

As you can see it counts all sales (orders) of the product no matter if there is a 0-Return.

Now I want to achieve that sales which have no returns (0-Value in Column C) are EXCLUDED from the Count of Returned Quantity but it should still be INCLUDED in the Count of Sent Quantity. Therefore, my desired result would be the following:

            Count of Sent Quantity       Count of Returned Quantity
Product A          3                             2
Product B          2                             1
Product C          1                             0

Do you have any idea how I can achieve this?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Michi
  • 4,663
  • 6
  • 33
  • 83
  • Possible duplicate of [Filter 0-values in PivotTable](https://stackoverflow.com/questions/45500860/filter-0-values-in-pivottable) – GlennFromIowa Oct 18 '17 at 18:15

1 Answers1

0

You need to insert Calculated field in your pivot which says if Returned Products>0 then 1 else o

and at pivot use sum and not count for this Field

BData
  • 189
  • 3
  • 10
  • This doesn't seem to work. It just returns a 0 or a 1, and doesn't sum the numbers. – bushell Jul 28 '17 at 08:40
  • The result of this solution in column "Count of Returned Quantity" is "1 1 0" so it refers to every sale only with 1 or 0 but does not make the 2 for example for Product A. – Michi Jul 28 '17 at 11:08
  • You can add a new column to your spreadsheet (let's say Column D). In that set a formula like if(c1>0,1,0) Then you can use Sum in your pivot using D column which will give you the result. – BData Jul 31 '17 at 02:17