Display result as a zero instead of a blank cell

Question

Hi,&nbsp;When the following formula have nothing to return a blank cell displays.&nbsp;&nbsp;COUNTIFS(Trades!$R$5:$R$1099,D37,Trades!$O$5:$O$1099,"&gt;0")&nbsp;SUMIFS(Trades!$O$5:$O$1099, Trades!$R$5:$R$1099, D37)&nbsp;What is needed to have a 0 instead of a blank shown&nbsp;"Show a zero in cells that have zero value" under Options/Advanced; is already ticked.&nbsp;Thanks&nbsp;&nbsp;

riny_van_eekelen · Answer

packie&nbsp;Both formulas return 0 for me if there's noting to count or sum. I suspect that you have a custom format (like General;General;) that suppresses zeros. Set the cell format to General and you should get zeros back.

packie · Answer

You are correct. The sheet Trades has the cell format set to General;General;I will want to keep the format General;General; though, is there a work around I could use?

riny_van_eekelen · Answer

packie&nbsp;Not really. If you do not want to show zeros as blanks, you can't not use that custom format.&nbsp;Of course, you could construct a formula like:&nbsp;=IF(countif_formula=0, "0", countif_formula)&nbsp;That will return a text "0" which will not be affected by the custom format. But why?&nbsp;

hansvogelaar · Answer

packie&nbsp;
The format General;General; explicitly suppresses 0 values because the 3rd section (after the 2nd semicolon) is empty.
I'd use General, as suggested by Riny_van_Eekelen but if you really want to, you could use General;General;General or General;General;0

Forum Discussion

Display result as a zero instead of a blank cell

4 Replies

Resources