Adding results of formula cells

**XH558** · 11-29-2011, 07:37 AM

Hi - Having been on many forums I know my question is probably answered somewhere in FAQs etc etc .. but I'm really in a hurry and need to get this very basic question sorted

I am building a spreadsheet which will be used to collate answers to given questions to give a total 'score' which can then be evaluated ...

My formulas all work like this ..... =IF(B5 ="X","5","0") and =IF(B6 ="X","4","0") etc
Meaning that if an X is put in one cell it scores 5 and if another it scores 4 and if nothing is entered it scores 0 .... all fine

But now I need to total the answers .... BUT - I can't use 'autosum' as although the cell displays a '5' or a '4' the cell actually only contains a formula

So how do I add up the response numbers to my formula?????

Thanks

David

**daddylonglegs** · 11-29-2011, 07:41 AM

Hello David,

Don't use quotes around numbers - excel then treats these as text and SUM ignores text. Change formulas so they look like this

=IF(B5="X",5,0)

**XH558** · 11-29-2011, 07:49 AM

BINGO ..... I knew it would be easy .....

Thank you so much - saved the day

**XH558** · 11-29-2011, 10:53 AM

Ok - I've moved on and the totals are now coming through fine - but the next thing is to equate the total scores to a numbered profile 1 to 10 - like so .....

20-27 1
28-35 2
36-43 3
44-51 4
52-59 5
60-67 6
68-75 7
76-83 8
84-91 9
92-100 10

But a formula that says if a number is > or < giving a profile answer seems to be limited to 7 in a string - but I've got to do 10

EG - =IF(A2>20<30,1,IF(A2>31<40,2, IF(A2>41<50,3,IF(A2>51<60,4,))))
This works but will only go to 7 brackets ....

Is there another way to do this ??

Come on - you know you want to