Sum points awarded if its applicable if not replace with maximum score

[Wardylewis]

Hi All,

I've developed a spreadsheet which is an audit document however some sections are not applicable to some sites.

My issue is that because the questions are not applicable, I don't assign a score which results in a percentage score of 0%. This has a negative impact on their overall score as the sheet cannot identify what is applicable and what isn't.

I have now added a line at the top of each sheet stating is it applicable Yes/No. What I am currently trying to achieve is a formula which says if applicable says yes count I:7 to I:99 if it says no assign maximum points found in G:101.

Any help would be greatly appreciated. I thought it might be a sumif or similar but cannot get an output result.

Thank You
Lewis

 

[steve the fish]

If 0% has a negative impact then surely 100% has a falsely positive impact.

 

[Wardylewis]

If 0% has a negative impact then surely 100% has a falsely positive impact.

Apologies its a compliance audit so it states you must meet the following criteria of A, B, C, D. If you don't it shows as 0% compliant and at the end of the audit it says a total of points available and then states how many you received and gives you a compliance % score.

I have got round it due to your reply as you are correct. I was thinking about it in the incorrect way I'm now doing a sumif formula on the score sheet which deducts the points that are available for that specific section which isn't applicable thus it isn't included in the final score.

Thank you