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

 

[jasonb75]

As long as all of the numbers are percentages you just need to sum the range then multiply it by 100 and change the cell format to number.

=SUM(A2:A5)*100

If you have a range of numbers of which some are not percentages then you will not be able to identify which are which.

 

[BORUCH]

As long as all of the numbers are percentages you just need to sum the range then multiply it by 100 and change the cell format to number.

=SUM(A2:A5)*100

If you have a range of numbers of which some are not percentages then you will not be able to identify which are which.

yes the problem is i have both types with percentage and without percentage how can i solve this

 

[mikerickson]

Can you post an example of some of your mixed data and the result that you want from it?

 

[BORUCH]

Can you post an example of some of your mixed data and the result that you want from it?

starting from cell A1 going down

12
4%
5%
35
10.00%
10%
56
65

 

[mikerickson]

What result do you want from that data?

 

[BORUCH]

Just a total of 197

 

[mikerickson]

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)

 

[CA_Punit]

=SUM(IF(A1:A8<1,A1:A8*100,A1:A8))

With Ctrl+Shift+Enter

 

[jasonb75]

Looking at ways to make it work if there are percentages greater than 100 or other values less than 1, I'm sure that there must be an easier way to pass a range larger than one cell to the CELL function, but this was the only thing that I could get to work.

I'm certainly not calling this a solution, this was more to see if it could be done.

Book5
	A
1	12
2	4%
3	5%
4	35
5	125.67%
6	10%
7	0.25
8	65
9	256.92
Sheet8

Cell Formulas
Range	Formula
A9	A9	=SUMPRODUCT(IF(LEFT(CELL("format",INDIRECT("A"&N(IF({1},ROW(A1:A8))))))="P",100,1)*A1:A8)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.