Distinguishing between a "0" and a "blank" cell

[blujeans]

The following formula looks through a list of names (in this case, looking for “Joe Smith”) in the monthly tab (in this case, “MARCH”) from column B and enters the calculated value associated with that name from column G. The formula works fine, but when there is no value in column G, it returns a 0% (format in the ‘receiving’ cell is set for %). This would be fine, but since it does not distinguish between a true 0% and a blank cell, this is causing a problem. Your thoughts are greatly appreciated!

=SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)

[pipoliveira]

Hi bluJeans,

Have you tried the following:

=IF(AND(SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)=0,SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)=""),"",SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31))

Let me know if that worked as desired.

Kind regards,
Filipe

[satabp]

Hi Filipe,

As per your condition, you never get "blank" because you have used AND operator, so the both the conditions never TRUE, as we dont get "blank" as output for any sumif function.

Thanks for the answer.

[satabp]

Hi,

May be try this....

=if(countif(MARCH!B2:B31,"Joe Smith")=0," ",(SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31))

Thanks,
Bhanu

[pipoliveira]

Ok then,

Have you tried:

=IF(SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)=0,"Define here what you need to show if the Sum = 0",SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)

Hope this one is fien for you.

Cheers,
Filipe

[satabp]

Hi Filipe,

Following may be work out...


=if(countif(MARCH!B2:B31,"Joe Smith")=0," ",(SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31))

Thanks,
Bhanu

[blujeans]

These sure seem like they should work, but did not. So I went back to my calculations in the G column in the monthly tab, thinking perhaps that is what is causing the problem:
=IFERROR(SUM(C2+E2)/F2,"")

I changed the “” result to “x” –hoping that would be easier to distinguish in the monthly tab; then tried changing your suggestion to:

=IF(AND(SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)=0%,SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31)="x"),"",SUMIF(MARCH!B2:B31,"Joe Smith",MARCH!$G$2:$G$31))

The result was still “0%” whether there was an “x” in the monthly tab or if it was “0”.

I'm guessing I made a silly mistake somewhere!

[shg]

SUM ignores text.

If you want nothing to appear when the sum is zero, use your original formula and format the cell as 0%;-0%;

[Dilksy99]

Hi,

I did a little mock up scenario with multiple drop cells (yellow cells), the value returned is a percentage and where 0% is returned, shows as blank ...

=IF(VLOOKUP($A2,INDIRECT($B2&"!A:K"),7,0)/100=0,"",VLOOKUP($A2,INDIRECT($B2&"!A:K"),7,0)/100)

... hope this helps!