Hi Alan, when I put a number in AF21 it counts in AF1, as you also discovered. When I then delete the number in AF21, it still counts the value in AF16. I don't know if that is what you are looking for? Otherwise you have to add something like IF(AF21<>"",AF16+AF21,""). That make it look a little better I think.

Frans

Hello Alan,

You are getting this #VALUE! error because you are trying to add a number value (cell AF16) to a text value (cell AF21, as the result of the formula is "" it is text). If you instead of using "" as a result for false use 0, then you will see a correct result in cell AF1.

Hi Frans,

Thanks for the reply, the formula you suggested does work but on the sheet instead of displaying the #value error its displaying the word FALSE instead.  I guess that is the correct result of the formula but I don't want anything displayed in the cell adding the values except for the actual value.  If there are no values in any of the cells in the formula then the cell should simply be blank. 

If you look in the attached you will see I added "Sheet1" with the same type of calculation shown in cell E1, this one behaves the way I want it to and the way I always thought Excel would behave, so I am thinking there is something on the WL Gantt sheet that is changing how the formulas work/display.  Any ideas?

Alan

You could modify the formula to check if the cell is blank before adding.

=AF16+IF(AF21="",0,AF21)

But I would prefer your formula in AF21 to show =IF(AF$19=1,$B21,0) like what Anders suggested.

You can then format AF21 to hide the 0.

Hi guys,

Many thanks for all the replies, I went with Sunny's formula suggestions and that works.

Appreciate all the help!

Alan