Is there any way to get a “sum” function to recognize an error value as zero? IOW you have a formula in place to sum a certain column of numbers. In some scenarios some of these numbers will be errors (#N/A, #DIV/0, etc.). In these cases, you want your sum formula to produce the sum of all the non-error numbers, while treating the errors as zeros. Is this possible?
(I am aware that you could write an “Iserror” function in the column itself, and convert all error values to actual zeros. But for my purposes I need to distinguish the errors from the actual zero values - I just want the sum function to work anyway).
Say the column of data that you want to add is C2:C40, and some of those cells are errors. You want the sume to appear in cell C42 (i.e. under all the data).
Why don’t you put formulas in a hidden column (say BB) that is the =IF(iserr(Cx)=FALSE,Cx,0), then your sum formula in C42 would be =SUM(BB2:BB40). You get to sum all the true values, but also still show the errors close to the data sum…
I do not think you can get the SUM() function to ignore an error. The way to get around that would be to define the cells in such way that they cannot have an error by using IF( blah blah bla is error, 0, blah blah blah)
And while we have the Excel experts checking in, I have another question. I seem to remember there is a way to have a cell show a check mark. I want the cells to show a check mark rather than “YES” or some other word. Is this possible? How about a green check mark or a red “X”?