Excluding from a sum formula numbers like 234.50%

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

123.00
 34.99
236.99
124.66%   this is actually how the number is and is not formatted
334.78
22.56

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
Comment
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
Commented:
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:
=SumNoPC(F1:F4)

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
Next
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.

Brad
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:

=CELL("format";A1)
=CELL("format";A2)
....


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:

   SUMIF(B1:B6;"G";A1:A6)

Author

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

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