Home

Blank not zero

%3CLINGO-SUB%20id%3D%22lingo-sub-686602%22%20slang%3D%22en-US%22%3EBlank%20not%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-686602%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20formula%26nbsp%3B%3DIF(OR(C3%3D0%2C%20H3%3D0%2C%20M3%3D0%2C%20R3%3D0%2C%20W3%3D0%2C%20AB3%3D0)%2C%20%22Yes%22%2C%20%22No%22)%20to%20test%20for%20zeroes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20one%20of%20the%20cells%20is%20blank%20I%20do%20not%20want%20to%20count%20this%20as%20a%20zero%2C%20but%20rather%20have%20it%20ignored.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20much%20appreciated%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-686602%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-687287%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20not%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-687287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358771%22%20target%3D%22_blank%22%3E%40Carl_Orlowe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT((INDEX(3%3A3%2C%7B3%2C8%2C13%2C18%2C23%2C28%7D)%3D0)*NOT(ISBLANK(INDEX(3%3A3%2C%7B3%2C8%2C13%2C18%2C23%2C28%7D))))%2C%22Yes%22%2C%20%22No%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carl_Orlowe
Occasional Visitor

Hi, 

 

I am using the formula =IF(OR(C3=0, H3=0, M3=0, R3=0, W3=0, AB3=0), "Yes", "No") to test for zeroes.

 

If one of the cells is blank I do not want to count this as a zero, but rather have it ignored.

 

Any help much appreciated, 

 

Thanks

1 Reply

@Carl_Orlowe 

It could be

=IF(SUMPRODUCT((INDEX(3:3,{3,8,13,18,23,28})=0)*NOT(ISBLANK(INDEX(3:3,{3,8,13,18,23,28})))),"Yes", "No")