How do I replace #VALUE! error for blank cells with blank cell

[coast331]

The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.

=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))

Thanks so much.

 

[gnrboyd]

You might check to see if any of your cell references have a space in them instead of just being blank That would cause this error.

Using your exact formula, you can add an if statement combined with ISERR so that in the event your formula gives an error message, a 0 will populate the cell.

=IF(ISERR((SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))),0,(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29)))))

Are you sure you need all of those parentheses?

 

[barry houdini]

Your formula is the equivalent of

=SUM(E21:E22,E34)-SUM(E6,E12,E14,E28:E29)

If you write it like that you shouldn't get an error because of any blanks

If you only want to show a result when all of those cells contain numbers then you could amend to

=IF(COUNT(E6,E12,E14,E21,E22,E28,E29,E34)=8,SUM(E21:E22,E34)-SUM(E6,E12,E14,E28:E29),"")

 

[coast331]

When I plug in each of the responses that have been offered, it returns -35 when the answer with my original formula is 5.

Is this some sort of an error message as well?

 

[shemayisroel]

Try this

=IF(ISERROR((SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))),"",(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29)))))

 

[coast331]

Thank you but when I plug this latest change in, it returns 8 when all the cells are blank. I have been looking at the suggestions very closely and still can't figure out the remaining issue.

Thanks for your help.

 

[shemayisroel]

Hi coast331,

Could you place a copy of your worksheet so we may look at the workings as the formula I placed should return exactly the same result but only will return blank if it returns an error...

Thanks

 

[coast331]

I do not know how to attach my worksheet.

 

[Aladin Akyurek]

Do the cells in question house formulas?

Do you want a blank result when any one cell is blank or when all the cells are blank?

 

[coast331]

Yes, the identified cells do house formulas. I need a blank result when all the identified cells are blank. As long as D34 and D6 have data, the formula also should still work - D12, D14, D21, D22, D28 and D29 should =0 if blank.

I hope this helps to clarify my issue.

Thank You.