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
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
Thanks
Related:
- How do I not get the sum of a column when it contains text?
- How to delete a column in word - Guide
- Excel count occurrences of string in column - Guide
- Sum in french excel - Guide
- Tweetdeck expand column - Guide
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - 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.
