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
RayH - Apr 20, 2015 at 08:20 PM
Related:
- How do I not get the sum of a column when it contains text?
- Check sum error - Guide
- Sum column if another column equals ✓ - Excel Forum
- How to convert column to row in notepad++ ✓ - Excel Forum
- Excel copy column from one sheet to another automatically - Guide
- Which function is used to compare a cell value to an array of cells and return a value that matches the location of the value in the array, and is used when there are more than two columns in the array ✓ - Excel Forum
1 response
Create a module and paste this in code for a custom 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.
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.
