Weighted Questionnaire results

[Schoots]

Hi all,

I have a question regarding getting data / results out of a questionnaire where each question (Yes or No questions) represents a certain amount of points one can get.
I know it is not the best explanation hence i made the following table to make more clear what i mean.

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Points per Q[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Questions[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Total score[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]

3
​

[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]

9
​

[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]Client 4[/TD]
[TD][/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see, each total score is based on the answer of the questions times the points for that particular question.

To make it more difficult, there are a few questions which require a Yes answer based on another criteria but when that criteria is not met the question doesn't have to be answerd with yes. which leaves the following table

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Points per Q
[/TD]
[TD]

2
​

[/TD]
[TD]

3​

[/TD]
[TD]

1
​

[/TD]
[TD]

2
​

[/TD]
[TD]

​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD]

Criteria
Met?
​

[/TD]
[TD="align: center"]Questions[/TD]
[TD="align: center"]Q1[/TD]
[TD="align: center"]Q2[/TD]
[TD="align: center"]Q3[/TD]
[TD="align: center"]Q4[/TD]
[TD]

Total score
​

[/TD]
[TD="align: center"]Total score
possible[/TD]
[TD="align: center"]Percentage
OK[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Only when
Criteria is met[/TD]
[TD]

​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Client 1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD]

3
​

[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]37.5%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Client 2[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD]

8
​

[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]100%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Client 3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD]

3​

[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]50%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Client 4[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD]

4​

[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]66.66%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

As you can see Client 1 and Client 3 both have a score of 3 but since one has a higher possible score due to the criteria the percentage OK differs.

What i have done now is the following formula to get to that Percentage OK

=(IF(D4="Y";D1;0))+(IF(E4="Y";E1:0))+(IF(F4="Y";F1;0))+(IF(AND(G4="Y";B4="Y");G1;0))

This does work and gives me the desired result.
However it probably is way to complex (keep in mind that in the real file i have 28 questions) and the file is continuasly updated with at this moment in time 28 questions.
Pulling the formula down to add more respondents is no issue at all and works fine. the problem arrises when I want to add or remove a question, because then the formula has to be altered to work with the new question.

Also I'm not the only one working with the file and others, who do not know how to alter the formula want to add questions.

So the end question is:
Is is possible to get the desired results as i have them now in the J collumn but working with a dynamic formula / range or an array formula that allows for adding and removing questions.

also if someone knows how to do this, could you elaborate a little bit on how the solution works rather than only giving me the formula so that i can expans my excel knowledge and do things like this myself in the future.

Thank you in advance.