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.

Heliocracy wrote:
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))

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))







In article ,
Heliocracy wrote:

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.

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!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Heliocracy" wrote in message
...
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.

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

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)
"Heliocracy" wrote:

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.

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" wrote:

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.

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.

"xlmate" wrote:

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" wrote:

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.

Try { =IF(AND(ISBLANK(B2:D2),ISBLANK(F2)),"",SUM(B2:D2,F 2)) }

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

Cheers

Stu

"Heliocracy" wrote in message
...
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.