sumif returns blank if all cells blank

[Heliocracy]

Hi all, I have rows in which columns b-f may be blank or have numerical
values in them, and in column e of the same row I need to be able to sum
these numbers if there are any, but remain blank if ALL of columns b-f are
blank. The problem I'm having with:

{=IF(ISBLANK(B2:F2),"",SUM(B2:F2))} in cell e2

is that it returns "" if ANY of the cells in b2:f2 are blank. In other
words, I need a formula in e2 that sums b2:f2 if any of those cells are not
blank, but returns blank if all cells in b2:f2 are blank. If all cells in
b2:f2 contain zeroes, then e2 should contain a zero, but if all cells in
b2:f2 are blank, e2 should be blank.

Thanks in advance for your help.

 

[Glenn]

Hi all, I have rows in which columns b-f may be blank or have numerical
values in them, and in column e of the same row I need to be able to sum
these numbers if there are any, but remain blank if ALL of columns b-f are
blank. The problem I'm having with:

{=IF(ISBLANK(B2:F2),"",SUM(B2:F2))} in cell e2

is that it returns "" if ANY of the cells in b2:f2 are blank. In other
words, I need a formula in e2 that sums b2:f2 if any of those cells are not
blank, but returns blank if all cells in b2:f2 are blank. If all cells in
b2:f2 contain zeroes, then e2 should contain a zero, but if all cells in
b2:f2 are blank, e2 should be blank.

Thanks in advance for your help.

One way (array formula...enter with CTRL+SHIFT+ENTER):

=IF(SUM(LEN(B2:F2))=0,"",SUM(B2:F2))

 

[JE McGimpsey]

Are you sure you want it in cell e2? That will give you a circular
reference...


In any case:

=IF(COUNT(B2:F2)=0,"",SUM(B2:F2))

 

[Bernard Liengme]

Alternatives:
1) USE simple =SUM(range) but give the cell a custom format that does not
display zero, such as #;-#;;

2)=IF(COUNT(B2:F2),SUM(B2:F2),"")
If any cell is non-blank then COUNT is a number greater than 0 which Excel
treats as TRUE and computes the sum, else it displays a blank

I do hope you plan to put the formula somewhere other than E2. Since B2:F2
includes E2, you will get a circular reference error with the formula in E2!

 

[Sheeloo]

Put this in G2
=IF(AND(B2="",C2="",D2="",E2="",F2=""),"",SUM(B2:F2))

It will give you blank only if ALL cells are blanks.

You have mentioned that you want the result in E2... that will be a circular
reference as SUM(B2:F2) includes E2 also.

If you DO want it in E2 then use
=IF(AND(B2="",C2="",D2="",F2=""),"",B2+C2+D2+F2)

 

[xlmate]

try this formula

=IF(SUMIF(B2:F2,"<>""")=0,"",SUMIF(B2:F2,"<>"""))

If you place the formula in F2, you will encounter Circular Reference

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis

 

[Heliocracy]

Thanks, I did mean G2 rather than E2. The problem with your formula is that
if I enter zero in each cell B2:F2, it returns blank ("") instead of zero.
It should only return as blank when all cells in b2:f2 are blank.

 

[Stu]

Try { =IF(AND(ISBLANK(B22),ISBLANK(F2)),"",SUM(B22,F2)) }

I'm not sure how you were putting your example formula into E2 as it would
have created circular refernces...?

Cheers

Stu