N/a

[edrachel]

Hi, I have a workbook that I have many formulas in each cell. Until all criteria is met, the cell shows "N/A". This is OK but my one problem is when some of the cells have met the criteria and are filled with numbers, I want to be able to add the numbers in these cells. Example: If I want to add a range of cells K12:k68 but some of these cells have numbers and some of the cells in the range have n/a, then my answer is coming up "n/a". How do I get it to total and ignore the "n/a"s or see the "n/a"s as "0" so that it will add all numbers and give me a total. Any help will be appreciated. I hope this makes sense. Thanks.

[richardreye]

enclose each formula with

=if(iserror(YOUR FORMULA),0,YOUR FORMULA)

There are a number of addins that can perform this trick for you automatically including PUP v6 found at http://j-walk.com/ss/pup/pup6/index.htm (this is free for a 30 day trial)

if you don't want to pay for PUP v6 then I use this macro to get rid of errors for the current selection of cells

Sub AddFormulaErrorCheck()

Dim sFormula As String

For Each C In Selection
If Left(C.Formula, 1) = "=" And Not Left(C.Formula, 11) = "=IF(ISERROR" Then
sFormula = Right(C.Formula, Len(C.Formula) - 1)
C.Formula = "=if(iserror(" & sFormula & "),0," & sFormula & ")"
End If
Next C

End Sub

HTH