Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

=SUMIF(Auto_Tracked!B1:B300,"rcumbaa",Auto_Tracked!D1:D300)

I would like it to show blank rather than 0 if possible

share|improve this question

4 Answers 4

up vote 1 down vote accepted

Simply place your formula in an IF statement to give you a blank cell if the result is 0.

Updated code:

=IF((SUMIF(Auto_Tracked!B1:B300, "rcumbaa",Auto_Tracked!D1:D300))=0,"",(SUMIF(Auto_Tracked!B1:B300, "rcumbaa",Auto_Tracked!D1:D300)))

This will check to see if the calculation results in a 0, and leave the cell blank if it is true. Otherwise, it will use the calculation results.

share|improve this answer
    
I get an error in this formula –  Tom Sep 10 '12 at 16:34
    
I updated the code. This one is tested. –  CharlieRB Sep 10 '12 at 16:54
    
=IF(SUMIF(Auto_Tracked!B9:B308,"tfavaro",Auto_Tracked!D9:D308)=0,"",(SUMIF(Auto_‌​Tracked!B9:B308,"tfavaro",Auto_Tracked!D9:D308))) –  Tom Sep 10 '12 at 17:32

You could hide zeroes with a conditional format. Click Conditional Formatting > New Rule and select Format only cells that contain.... Make the rule Cell value is 0 and set the text color to the same value as the background color (usually white).

share|improve this answer

I would format the cells to not show a zero value. Right click on the cells that you want to hide the zero in and apply the formatting for the positive value followed by a ; negative values; zero values. In the example below, the positive values show the cell color, negative values are red and the zero values are blank.enter image description here

share|improve this answer
    
format would not work in this case because I need 0's in other cells, I'm looking for something I can add to the formula like +if=0,"", - but not sure how to –  Tom Sep 10 '12 at 14:38
    
You can format each individual cell. Don't format those cells that you want a "0" in. –  wbeard52 Sep 11 '12 at 4:35

This can be easily achieved by a simple formula:

=TEXT(SUMIF(Auto_Tracked!B1:B300,"rcumbaa",Auto_Tracked!D1:D300),"#")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.