How do I not get the sum of a column when it contains text?
Closed
xrqexcel
RayH - Apr 20, 2015 at 08:20 PM
- Posts
- 1
- Registration date
- Saturday April 11, 2015
- Status
- Member
- Last seen
- April 11, 2015
RayH - Apr 20, 2015 at 08:20 PM
Related:
- How do I not get the sum of a column when it contains text?
- Special sum of a column ✓ - Forum - Excel
- Excel sum if other column ✓ - Forum - Excel
- Check sum error - Guide
- How to sum a list of names in excel - Guide
- Excel if cell color is red then sum ✓ - Forum - Excel
1 reply
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.