List all values in a table excluding zeros


Board Regular
Sep 13, 2012
Hi everyone

I'm not sure how to explain this properly so my apologies if I don't. Please feel free to ask for clarification if you might be able to help.

I have a table from a report that's generated by a program we use in this office that lists all sales made by sales people. The columns include a sales person's name, the magazine they work on, revenue they've generated from a sale and the volume of that sale (as well as several other columns that aren't important).

JohnFishing weekly4000.002.00
AnneSkydiver's monthly0.001.00
JillPC User's bible5000.002.50


I create a pivot table from this report that gives me total sales, revenue and volume. The problem I'm trying to tackle is that sometimes revenue is zero but there is still volume (like Anne, in the example above) so I don't want to include the volume for those sales in the pivot table as they artificially inflate the volume that a person has actually sold.

I don't know if there's a way to get the pivot table to ignore any entries with zero revenue or if I need to use some sort of array formula on another sheet to create another table that doesn't list any of the zero-revenue sales and then create a pivot table from that list.

Please forgive me if I'm not explaining this clearly as I'm not an Excel wiz (yet!). Any help that anyone can give me would be greatly appreciated!


Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I am not to versed in Pivot Tables but you can do what you need with a SUM or SUMPRODUCT function. Your Pivot Table could be your data sheet and you could have an info sheet that would give you the proper sums of each employee.
Upvote 0
May Be if it Can hel you.

This formula elliminate 0 end will give you the (Revenue 4000 3000 5000) but you need to pass the column in which you to eliminate the zero.

=INDEX($D$13:$D$17,SMALL(IF($D$13:$D$17<>0,(ROW($D$13:$D$17)-MIN(ROW($D$13:$D$17))+1),""),ROW(A1))) with Ctrl+Shift+Enter

Upvote 0
hi, Rusty

Please stay with the pivot table - array formulas are not the way to go.

If it suits, simplest way might be to add a new field to the data. Header "Show It" and formula linked to the Revenue field. =revenue field >0

This will return either TRUE or FALSE. Then in the pivot table make the "Show It" field a page field and set it to TRUE. This will filter out the records with FALSE.

FYI, a more complex way is available without adding an extra field - it involves filtering out the zero value revenue fields via SQL.

Upvote 0
Hi everyone, thanks so much for your help, it's much appreciated.

Fazza, very simple and effective solution, thanks very much! I think sometimes I complicate things when all I need to do it think a little more laterally.

Thanks again to everyone!
Upvote 0
hi, Rusty

Just in case.

What I suggested did not match your criteria so you may want to change it. You asked to exclude revenue of zero and the formula I gave excluded zero and negative values. =revenue>0

To do what you asked, exclude zero, then the formula should instead =revenue <> 0. [ Or some variation of that if there were the chance of very small numbers that should be treated as zero. Numbers like 0.0000001. So then a formula like =abs(revenue)<0.0001 ]

Upvote 0
Thanks for the extra info, Fazza. We never have negative revenue figures or tiny figures like 0.000001 on the report as only includes currency for bookings taken so =revenue>0 worked fine. But I appreciate the extra info, I've just learned a little more! Thanks again!!
Upvote 0

Forum statistics

Latest member

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
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 "".
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