I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is '0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1 is
"#VALUE!". How can I make the target cell (G1) blank while still having the
formula.

Please help.
Rum

Try

=IF(COUNT(A1:F1)=0,"",SUM(A1:F1))

If this post helps click Yes
---------------
Jacob Skaria


"Rum" wrote:

I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is '0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1 is
"#VALUE!". How can I make the target cell (G1) blank while still having the
formula.

Please help.
Rum

1: =IF(COUNT(A1:D1),SUM(A1:D1)."")
That is a pair of double-quotes with nothing between

2. That happens with =A1+B1+C1+D1 but not with =SUM(A1:D1)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Rum" wrote in message
...
I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is
'0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1
is
"#VALUE!". How can I make the target cell (G1) blank while still having
the
formula.

Please help.
Rum

Typo!! the period should be a comma, of course
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Bernard Liengme" wrote in message
...
1: =IF(COUNT(A1:D1),SUM(A1:D1)."")
That is a pair of double-quotes with nothing between

2. That happens with =A1+B1+C1+D1 but not with =SUM(A1:D1)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Rum" wrote in message
...
I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is
'0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1
is
"#VALUE!". How can I make the target cell (G1) blank while still having
the
formula.

Please help.
Rum

=IF(SUM(A1:F1),SUM(A1:F1),"")


"Rum" wrote:

I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is '0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1 is
"#VALUE!". How can I make the target cell (G1) blank while still having the
formula.

Please help.
Rum