Excluding from a sum formula numbers like 234.50%

PeterWhitts used Ask the Experts™
in Excel 2007 I have a column with numbers in like:

124.66%   this is actually how the number is and is not formatted

What I need is a sum type formula that covers the whole column range of numbers but excludes all numbers that end in % from the total

Many thanks
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
The SUM function excludes text that looks like numbers. I therefore conclude that the percentage values in your data are actually numbers formatted as percentages.

If so, you can distinguish them using a user-defined function with a worksheet formula like:

Install the user-defined function in a regular module sheet (not ThisWorkbook or the code pane for a worksheet).

Function SumNoPC(rg As Range) As Double
Dim cel As Range
Dim d As Double
For Each cel In rg.Cells
    If (IsNumeric(cel.Value)) And (Right(cel.Text, 1) <> "%") Then
        d = d + Val(cel.Value)
    End If
SumNoPC = d
End Function

Open in new window

This is one of those questions that could probably benefit by a sample workbook that illustrates your problem. If the above code is not working, please consider posting a sample workbook.

Assuming the numbers are in the cell range A1:A6

If you add a column to the right of the numbers which has the following formula:


Which will show G for GENERAL format and P2 for PERCENT with two decimals

Then you can use the following formula to exclude percentages from the sum:



Thanks Brad
Thats seems to work very well.
Nice to hear from you again.
Best wishes

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial