Adding percentages that are produced by formulas

[28creation]

I can't believe you can't just add percentages with the usual =SUM(A1:A10) formula!!!

The percentages I want to add are all created by formulas, not typed into the cell itself. The total percentage at the bottom of the page is adding up the individual weightings from a number of entries.

How can this be done please?

Thanks in advance.

 

[sheetspread]

Shouldn't you multiply the columns?

Excel 2010
	O	P	Q	R
8	EXCEEDING	20%	OUTSTANDING	25%
9
10
11
12
13	11	8%	6	8%
14	11	4%	6	4%
15
16
17	0	5%	1	4%
18
19
20
21
22	1	8%	2	6%
23	PASS	10%	DEV	5%
24	DEV	5%	FAIL	0%
25
26		1.4		0.9
Sheet11

Cell Formulas
Range	Formula
P26	=SUMPRODUCT(O13:O22,P13:P22)
R26	=SUMPRODUCT(Q13:Q22,R13:R22)

 

[28creation]

I don't understand why some of this works & some it doesn't....

If I do =SUM(P8:P9,P11:P15,P17:P18,P22:P24) or =SUMPRODUCT(P8:P9,P11:P15,P17:P18,P22:P24), they bring back the same results.

The same with =SUM(P8:P24) & =SUMPRODUCT(P8:P24). Doesn't matter if there are empty cells in there.

Some of the cells get added up & others don't. Very strange.

Any help is much appreciated.

 

[sheetspread]

=SUM(P8:P9,P11:P15,P17:P18,P22:P24) or =SUMPRODUCT(P8:P9,P11:P15,P17:P18,P22:P24), they bring back the same results.

Not when I try it:

Excel 2010
	O	P	Q	R
1
2
3
4
5
6
7
8	EXCEEDING	20%	OUTSTANDING	25%
9
10
11
12
13	11	8%	6	8%
14	11	4%	6	4%
15
16
17	0	5%	1	4%
18
19
20
21
22	1	8%	2	6%
23	PASS	10%	DEV	5%
24	DEV	5%	FAIL	0%
25
26		60%	#VALUE!
Sheet11

Cell Formulas
Range	Formula
P26	=SUM(P8:P9,P11:P15,P17:P18,P22:P24)
Q26	=SUMPRODUCT(P8:P9,P11:P15,P17:P18,P22:P24)

The same with =SUM(P8:P24) & =SUMPRODUCT(P8:P24)

Yes, it's a single column, you're not multiplying by anything, just adding

 

[28creation]

Can it be it's not including some of the cells because of the formulas in them?

 

[28creation]

I'm trying to work out how to paste the cells so you can see what I mean but it won't let me.

The cells that aren't getting added up have these formulas:

=IF(O8="","",IF(O8="OUTSTANDING","25%",(IF(O8="EXCEEDING","20%",(IF(O8="ACHIEVING","15%",(IF(O8="DEVELOPING","10%",(IF(O8="UNACCEPTABLE","5%"))))))))))

=IF(O13="","",(IF(O13>=12,"10%",(IF(O13=5,"8%",(IF(O13=6,"8%",(IF(O13=7,"8%",(IF(O13=8,"8%",(IF(O13=9,"8%",(IF(O13=10,"8%",(IF(O13=11,"8%",(IF(O13=4,"6%",(IF(O13=2,"4%",(IF(O13=3,"4%",(IF(O13=0,"2%",(IF(O13=1,"2%")))))))))))))))))))))))))))

=IF(O14="","",(IF(O14>=12,"5%",(IF(O14=5,"4%",(IF(O14=6,"4%",(IF(O14=7,"4%",(IF(O14=8,"4%",(IF(O14=9,"4%",(IF(O14=10,"4%",(IF(O14=11,"4%",(IF(O14=4,"3%",(IF(O14=2,"2%",(IF(O14=3,"2%",(IF(O14=0,"1%",(IF(O14=1,"1%")))))))))))))))))))))))))))

=IF(O22="","",(IF(O22=0,"10%",(IF(O22=1,"8%",(IF(O22=2,"6%",(IF(O22=3,"6%",(IF(O22=4,"6%",(IF(O22=5,"6%",(IF(O22=6,"4%",(IF(O22=7,"4%",(IF(O22=8,"4%",(IF(O22=9,"4%",(IF(O22=10,"4%",(IF(O22>=11,"2%",)))))))))))))))))))))))))

=IF(O23="","",IF(O23="PASS","10%",(IF(O23="DEV","5%",(IF(O23="FAIL","0%"))))))

=IF(O24="","",IF(O24="PASS","10%",(IF(O24="DEV","5%",(IF(O24="FAIL","0%"))))))

 

[28creation]

All of these formulas work & display correctly but for whatever reason are not being added up at the bottom.

 

[philwojo]

I am no expert, but the way those formulas give you a percentage is not a number it is text, you have each percentage value surrounded by quotes. So the SUM or any formula won't work because those values are treated as text and not numeric.

You can try and change those to numeric values by removing the quotes, then your SUM formula should work.

 

[28creation]

Ahhhhhh!! that makes total sense, thank you so much!

 

[sheetspread]

You said the percentages are formula results, so they should be numbers not text. Can you show a sample of the data and formula?