Adding cells with numbers and ""

[teachman]

Hello folks, This is my first post to this forum. I am reading/using a MrExcel book that is very helpful.

I have an issue that I haven't seen discussed on the forum (lurking here for a few months).

I have several cells that are dependent upon cells in another worksheet using the basic formula: IF(Sheet1!B2="","",Sheet1!B2) the reference (B2) changes in each cell of the current worksheet that references Sheet1. The cells in the current worksheet are formatted as numbers. Based on what is in the referenced cells on Sheet1 the value could come back as a number of some kind or "".

Now, I want to change the value of another cell based on the sum of the cells above. Since one or more of the current worksheet cells may have "" as the entry I get a #Value error when I try to add the cells.

As an example the current worksheet has: B7=0, C7=0, D7="", E7=0. When I SUM(B7:E7) I get the #VALUE error. A "" cell is effectively 0. I need to find a way to add these cells together.

Thanks,

Teachman

 

[Weaver]

Which version of Excel is that? I'm using 2002 and don't have that problem. =SUM() just appears to work fine where there are formulas evaluating to "" in the range.

Are you sure there's not some other problem?

 

[Expiry]

I suspect that whoever set that formula up in the first place just wanted to display 0 as a blank. You could,, therefore change the formula to =sheet1!B2.

If sheet1!B2 was blank, then you would get a 0. If you don't want to see a 0, then change your cell formatting to ;;"" in Format, Cells, Custom.

Your sum will work.

 

[teachman]

I am using 2007 Excel. Thanks for the replies. They helped me find a way to do what I wanted.

You guys are great!

Thanks,

Teachman