In Sharepoint, if you are trying to create a calculated value, you may need ‘isnull’ logic. In the case I was working on, had three columns:

1 2 3 |
Year1_Spend Year2_Spend Year3_Spend |

In a fourth column called “LRP_Total_Cost”, I wanted to add these three values. If each column had a number in it, the formula is easy:

1 |
=sum(Year1_Spend,Year2_Spend,Year3_Spend) |

But if a column was blank, the formula wouldn’t work. One way to handle this is to have a default value of 0 (zero) in the column and to make sure all cells are populated.

However, from a user point of view, blank means zero and visually, not showing zero makes the output easier to read.

So I could set all my output screens and reports to hide the value if it is zero, or I can create a formula that assumes a blank cell is zero. I went with the formula:

1 2 3 4 5 6 |
= sum( (IF(ISNULL(Year1_Spend),0,Year1_Spend)), (IF(ISNULL(Year2_Spend),0,Year2_Spend)), (IF(ISNULL(Year3_Spend),0,Year3_Spend)) ) |