SUM

%3CLINGO-SUB%20id%3D%22lingo-sub-485140%22%20slang%3D%22en-US%22%3ESUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485140%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20here%20is%20a%20sample%20of%20my%20table.%3C%2FP%3E%3CP%3EI%20want%20to%20remove%20the%200%20in%20the%20H%20and%20put%20BLANK%20values%20when%20there%20is%20nothing%20to%20SUM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110607iF19AD5FD59E95D56%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Pb%20in%20Excel.PNG%22%20title%3D%22Pb%20in%20Excel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-485140%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-485164%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485164%22%20slang%3D%22en-US%22%3EI%20already%20answered%20that%20same%20question%20earlier.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-485235%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-485235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327988%22%20target%3D%22_blank%22%3E%40Amat3ur%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20that%20is%20common%20but%2C%20from%20my%20point%20of%20view%2C%20not%20quite%20good%20idea%20to%20return%20empty%20text%20(%22%22)%20instead%20of%20zero%20to%20show%20the%20value%20as%20blank.%20Better%20to%20avoid%20mix%20of%20texts%20and%20numbers%20in%20one%20column.%20If%20one%20day%20you%20decide%20to%20do%20any%20calculations%20on%20column%20H%20you%20may%20receive%20an%20errors.%20SUM%20and%20some%20other%20functions%20ignore%20texts%2C%20but%2C%20for%20example%2C%20if%20take%20some%20percentage%20in%20column%20I%20when%20%3DH4*5%25%20returns%20an%20error%20if%20you%20have%20empty%20text%20in%20H4.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20show%20zeroes%20as%20blanks%20it%20could%20be%20using%20the%20setting%20in%20File-%26gt%3BOptions-%26gt%3BAdvanced-%26gt%3BDispaly%20options%20for%20worksheet.%20Even%20better%20to%20apply%20custom%20format%20General%3BGeneral%3B%20or%20similar%20for%20not%20showing%20zeroes.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Amat3ur
New Contributor

Hi, here is a sample of my table.

I want to remove the 0 in the H and put BLANK values when there is nothing to SUM

 

Pb in Excel.PNG

 

Many thanks,

 

2 Replies
I already answered that same question earlier.

@Amat3ur ,

 

By the way, that is common but, from my point of view, not quite good idea to return empty text ("") instead of zero to show the value as blank. Better to avoid mix of texts and numbers in one column. If one day you decide to do any calculations on column H you may receive an errors. SUM and some other functions ignore texts, but, for example, if take some percentage in column I when =H4*5% returns an error if you have empty text in H4.

 

To show zeroes as blanks it could be using the setting in File->Options->Advanced->Dispaly options for worksheet. Even better to apply custom format General;General; or similar for not showing zeroes.