Filter 0-values in PivotTable

Question

I have the following Excel spreadsheet:

      A                  B                    C
    Product         Sent Quantity       Returned Quantity
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 sales (Column B) an returns (Column C) of a product.

I created a Pivot-Table out of the data above which leads to the follwoing result:

                Sum of Sent Quantity          Sum of Returned Quantity
Product A           1.500                          700
Product B             550                          150
Product C             700                            0

Now I use the Returned Quantity as a Report Filter. I set "150" as the filter criteria and I get the following result:

                 Sum of Sent Quantity          Sum of Returned Quantity
Product B             550                          150

So far everything works fine.

Now I change the filter from "150" to "0" and I get the following result:

                Sum of Sent Quantity          Sum of Returned Quantity
Product A              500                            0
Product B              250                            0
Product C              750                            0

However, my target result is:

              Sum of Sent Quantity          Sum of Returned Quantity
Product C              700                            0

What do I have to change to reach my target result?

i tried the same this and i got 700 for Product C. Kindly check your pivot once again. — Shrikant, Aug 04 '17 at 09:02
You filter it based on `Returned quantity` not on sum of it. That's the reason. — Michał Turczyn, Aug 04 '17 at 09:18
@Michał Turczyn: How can I change the filter to the "sum of it"? Well, it does not have to be necessarily a Pivot Table. I think another approach would be an array formula. — Michi, Aug 04 '17 at 11:16
What's with the downvote on this question? Aside from a minor spelling error, this seems to be an excellently stated and researched question! — GlennFromIowa, Oct 18 '17 at 18:12

score 0 · Accepted Answer · answered Aug 04 '17 at 11:33

0

So, I have entered your data in "Sheet1" like this:

Then, in "Sheet2" I made folowing table:

Which is your pivot table. But cells are defined with formulas:

B2: =SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$B$2:$B$7)

C2: =SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$C$2:$C$7)

The rest is just matter of draging it down (it will adjust automatically, as you know). Now, just lock first row and then you can apply any filters to it.

answered Aug 04 '17 at 11:33

Michał Turczyn

32,028
14
47
69

O.k. that is certainly a solution to go with in my case above so I will give it as a correct answer. However, the issue is that in my original file I have more than just Product A-C. It could happen that I have 1.000 or more products so I would have to go with "Remove Duplicate" and then put SUMIF. Seems there is no way to solve this within a PivotTable. – Michi Aug 04 '17 at 12:06
It's hard with pivot table. But you pointed right wau of doing it. Just take all products from the list, in another sheet paste only unique values and then apply formula I gave you. – Michał Turczyn Aug 04 '17 at 12:08

score 0 · Answer 2 · answered Oct 18 '17 at 17:59

Here's a slightly different way to do it, which only requires one table, and allows you to filter and display values in your pivot table:

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

The formula in cell D2 is =SUMIF($A:$A,$A2,C:C) Copy this formula down the column.

Then set Sum of Returned Quantity as your filter, and it should work correctly. Additional columns may be added for additional filters and you can hide the subtotal column(s) if you wish.

Filter 0-values in PivotTable

2 Answers2

Linked