Avatar of PeterWhitts
PeterWhitts
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excluding from a sum formula numbers like 234.50%

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
Microsoft Excel

Avatar of undefined
Last Comment
PeterWhitts

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
yogi4life

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)
PeterWhitts

ASKER
Thanks Brad
Thats seems to work very well.
Nice to hear from you again.
Best wishes
Peter
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck