I need a formula to total up scores in intermittent cells which have been populated by selcting answers from drop down lists

[Susan1968]

This is my first post so please accept my apologies if I have posted this question incorrectly . I am using Excel 2010 and I have produced a check sheet form for work in which questions are answered from drop down list in cells C13:15,19,21,23,25,29,31,31,35 and this generates a 1 or 0 answer in the corresponding cells in column E. I want a formula to automatically add the scores up and show this in cell B60. I have linked cells A to D 61,62,63,64,65 as a table of automatic final outcomes based on the number of the score generated by responses to questions in C13:C35.As it stands at the minute if I manually total the score and fill cell B60 the correct row in the table is highlighted to provide the automatic response. I can attach the form if that would help. Thanks and sorry for if the question is too long

 

[VBA Geek]

Hi Susan, usually it helps if next to the quest you post a sample of your data so people can help you better

 

[Susan1968]

Hi VBA Geek and thanks for the advice. I hope this is what you mean in cells E13:E35 the formulas vary depending on choices in corresponding questions in cell C13:C35 but all are similar to =IF(C15="yes","1",IF(C15="n/a","1",IF(C15="neighbour","1",IF(C15="family member","1",IF(C15="new occupier","1",IF(C15="child","1",IF(C15="Left letter","1",IF(C15="no","0","")))))))) ... this produces a 1 or 0 answer when one of the drop down options are chosen. Cells A61:D61 have conditional formatting to change them to red using following formula =ISNUMBER(SEARCH("9",B60))=TRUE ..hope this helps thanks.

 

[Susan1968]

in addition to my other posts my ultimate aim is to enable sers of the form to select the appropriate option in drop down lists (C13:C35) and as a result totalscore is calculated automatically in to D:60 and then the score feedback table A61:A65-D61:D65 is highlighted based on figure in D:60. this will make results objective and not subjective. thanks

 

[VBA Geek]

hi - from just the formula I can't understand the problem, maybe post a sample of your excel table ?

 

[Susan1968]

Hi VBA Geek, thanks again for your response... Would it be easier/possible to send you my file? Thanks

 

[Aladin Akyurek]

Hi VBA Geek and thanks for the advice. I hope this is what you mean in cells E13:E35 the formulas vary depending on choices in corresponding questions in cell C13:C35 but all are similar to =IF(C15="yes","1",IF(C15="n/a","1",IF(C15="neighbour","1",IF(C15="family member","1",IF(C15="new occupier","1",IF(C15="child","1",IF(C15="Left letter","1",IF(C15="no","0","")))))))) ... this produces a 1 or 0 answer when one of the drop down options are chosen. Cells A61:D61 have conditional formatting to change them to red using following formula =ISNUMBER(SEARCH("9",B60))=TRUE ..hope this helps thanks.

In order to replace the long formula...

Create a range from A2 on a sheet called Admin, with:

yes
n/a
neighbour
...

Select this range and name it as List via the Name Box.

Now you can just say:

=ISNUMBER(MATCH(C15,List,0))+0

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

Formulas:

In E13 - use only Enter to enter the formula

=IF(OR(C13={"yes";"n/a";"neighbour";"family member";"new occupier";"child";"Left letter"}),"1",IF(C13="no","0",""))

In B60 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(IF(ISNUMBER(-E13:E35),--E13:E35))

Markmzz

 

[Susan1968]

Thanks Markmzz... that worked superbly x

 

[Susan1968]

Thanks Aladin... much easier