Previous Topic Next Topic

How do I not get the sum of a column when it contains text?

Closed
xrqexcel
Posts
1
Registration date
Saturday April 11, 2015
Status
Member
Last seen
April 11, 2015
 - Apr 11, 2015 at 08:50 PM
 RayH - Apr 20, 2015 at 08:20 PM
Hello All, I have an excel file in which I am adding the numbers of a column, but sometimes the column I am summing may have text in one of the cells instead of a number, when this occurs I want the 'sum formula cell' to show "error".
Thanks
Related:

1 reply

Answer 1 / 1
RayH
Apr 20, 2015 at 08:20 PM
Create a module and paste this in code for a custom function: 

Public Function newsum(rng As Range) As Variant
Dim t As Variant
Dim c As Range

For Each c In rng
If Not IsNumeric(c.Value) Or c.Value = "" Then
    Err = True
    Exit For
End If
    t = t + c.Value
Next

If Err Then
    newsum = "Error"
Else
newsum = t
End If
End Function


In Excel, use it by entering in the cell:

=newsum(A2:A20)

It will return "Error" whenever a non-numeric value or a blank field is in the range otherwise the total is displayed.
0