Excel tip: How to not show zero values for forumla result
October 2, 2007
Here’s a handy little formula to add up a column or row of figures and only show the result if it’s a non-zero.
I find it hard to pick out positive numbers in a whole list of values if there are a lot of zero numbers. Look at the two following examples..
if there are a whole lot of numbers in use like when you’re looking at a stock list that goes on for pages and you’re looking for what is left amongst a lot of zero quantity items, it’s really easy to miss the numbers. But, by using the IF function of Excel you can test the normal formula and only output a result for whatever value you want. ie.
=IF(logical test,if true,if not true)
the example below uses this
- logical test : = SUM(D4:E4)>=1
the sum of the numbers between D4 and E4 is greater than equal to 1
- if true: sum(D4:E4)
if the logical test is true then output the sum of D4 to E4
- if not true: “”
output nothing, you could use whatever you want for the “if true” and “if not true”. Text would go in quotes and formulas go without.
Here’s what it looks like: