I recently got a request through LinkedIn from someone I met at a conference who wanted to know how she could do the following..

She wanted to calculate exam results given the following parameters.

” If they do not fail any module but can now have up to a maximum of 3 modules that falls between 35 and <40 they can pass by compensation, my question is how do I get excel to understand it is up to 3 and no more. For example, if they have not failed any modules and have 3 or less modules that come in between the above they would ‘Pass by Compensation’, if they have not failed any modules but have 4 or more between 35 and <40 they fail. But if they fail any module, they fail regardless. ”

This required a combination of IF/And/CountIfs.

You can download the file here…Compensation pass fail question

And in this video I explain how I came up with the formula.

[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/compensation_pass_fail_pass.mp4″]