IF Statement returns 0 instead of blank

[nenandi]

How to solve this IF statement returns 0 instead of blank
=IF(SUM(D21:D27)>0,1,IF(OR(D21=0,D22=0,D23=0,D24=0,D25=0,D26=0,D27=0),0,IF(OR(D21="",D22="",D23="",D24="",D25="",D26="",D27=""),"")))
I want to show me empty cell for further average calculation.

 

[FormR]

Hi, the non-working formula does little to describe your intentions.

Here is a guess, if this isn't what you want, then a written description of what you do want would probably help.

=IF(SUM(D21:D27)>0,1,IF(COUNT(D21:D27),0,""))

 

[jtakw]

How to solve this IF statement returns 0 instead of blank
=IF(SUM(D21:D27)>0,1,IF(OR(D21=0,D22=0,D23=0,D24=0,D25=0,D26=0,D27=0),0,IF(OR(D21="",D22="",D23="",D24="",D25="",D26="",D27=""),"")))
I want to show me empty cell for further average calculation.

Hi,

The above part in BOLD don't make sense, if SUM of D21:D27>0, you want 1, but if Any cell between D21:D27 is 0, you want 0 ???

 

[bebo021999]

If expected outcome was either 1 or blank (not 0):

=IFERROR(1/(1/(SUM(D21:D27)>0)),"")

 

[nenandi]

If expected outcome was either 1 or blank (not 0):

=IFERROR(1/(1/(SUM(D21:D27)>0)),"")

also is need to have blanks

 

[nenandi]

Hi,

The above part in BOLD don't make sense, if SUM of D21:D27>0, you want 1, but if Any cell between D21:D27 is 0, you want 0 ???

yes if any one of them D21:D27 have 1 in D29 should be 1 if all of them have zeros in D29 need to be zero if column D skipped and all was blank in D29 need to be blank

 

[jtakw]

yes if any one of them D21:D27 have 1 in D29 should be 1 if all of them have zeros in D29 need to be zero if column D skipped and all was blank in D29 need to be blank

According to your description, then @FormR formula in Post # 2 should work for you.

 

[Alex Blakenburg]

if all of them have zeros in D29 need to be zero if column D skipped and all was blank in D29 need to be blank

This deviates from your original formula. Your original formula says that if the sum = 0 then if any ONE of the cells is zero you want 0. That is not the same as "if ALL of them have zero".
This is also the case for blanks.
Is it ALL or any 1 ?

 

[nenandi]

This deviates from your original formula. Your original formula says that if the sum = 0 then if any ONE of the cells is zero you want 0. That is not the same as "if ALL of them have zero".
This is also the case for blanks.
Is it ALL or any 1 ?

If all have zero then 0

 

[FormR]

if any one of them D21:D27 have 1 in D29 should be 1

if all of them have zeros in D29 need to be zero

all was blank in D29 need to be blank

What if some are blank and some are zero? is that possible?