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

Susan1968

New Member
Joined
Feb 4, 2014
Messages
29
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 :)
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Hi Susan, usually it helps if next to the quest you post a sample of your data so people can help you better :)
 

Susan1968

New Member
Joined
Feb 4, 2014
Messages
29
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

New Member
Joined
Feb 4, 2014
Messages
29
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857

ADVERTISEMENT

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

Susan1968

New Member
Joined
Feb 4, 2014
Messages
29
Hi VBA Geek, thanks again for your response... Would it be easier/possible to send you my file? Thanks :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top