sum numbers ignoring percentage

[BORUCH]

hi

is there a formula that will calculate a sum of just the numbers and ignore the percentage symbol for ex

i have numbers in cells that looks like this

4%
10.00%
5%
8%

i would like to just sum up all the numbers and ignore the percentage symbol

 

[mikerickson]

What is this list of un-ruley cell entries?
Do you have multiple users, some of whom forget to press the % key?

 

[BORUCH]

The CSE formula = SUMPRODUCT(IF(A1:A20<1,100,1)*(A1:A20)) should return what you want (This needs to be entered with Ctrl-Shift-Enter)

Hi

Sorry the delayed response that formula worked

I just have one more question,how can i add to this formals a scenario of a letter or multiple letters next to a number for ex .

2X
2EE
ETC..

It should sum it as a total of 4

 

[mikerickson]

If there is only one digit, an array formula like =SUM(0+MID(A1:A4&"0",1,1)) would work.
But better would be to control your data entry. Or to clean the data before analysis.

 

[BORUCH]

If there is only one digit, an array formula like =SUM(0+MID(A1:A4&"0",1,1)) would work.
But better would be to control your data entry. Or to clean the data before analysis.

Well the problem is i can't really control it its needed for other reason not for excel but i do sometimes have 2 letters after a number

and also how do i combine this formula with the earlier formula of = SUMPRODUCT(IF(A1:A20<1,100,1)*(A1:A20)) ?

thanks again for your help

 

[mikerickson]

Numbers before text, numbers after text, some as percentages, some as percentages without the %, some as numbers.
No, I'm not going to help cater to your boss's incompetence.

Clean the data you get. Helper columns, extra worksheets. Clean the data.