Monday, March 28, 2005

MS Excel: Sum of a column with #DIV/0! Or any other error message in some cells

My friend recently requested me to suggest some quick fix for sum of a column which has few cells containing #DIV/0! Or any other error message. Doing the sum function produces error because sum neither ignores those cells nor understands it.

My process to deal with this problem is:

1.Insert a column next to the column you are trying to sum up.
2.In my example, the first cell is A3, I inserted column B.
3.In Cell B3, I wrote the formula “=IF (ISERROR (A3), 0,A3)”
4.This formula looks for the cell value and if it is an error, converts it to 0.
5.Then I copied this formula in B4 to B6 since my values were in up to cell B6, you could copy it till last cell value.
6.Select Cells B3 to B6, copy and paste special-values only.
7.Now you can sum this column and you are done.

Example:
Formulas:



Values:

1 comment:

mahim said...

Good work! something very useful.