Help require to get perfect output if there is a 'H' remark

Hi friends,
If there is a ‘H’ remark in column ‘AY’ then I’m getting wrong output in column ‘CI’. All other conditions are working fine except this ‘H’ remark condition. I want to find out in how many subjects the student is fail.

Any help will be appreciated.

Thanking you in anticipation.

So is H only going to appear in AY column and not any other columns?

Your formula is double counting when H is in AY, that is it counts the number of fails below the threshold values you've put in each column then if column AY has a H in it it then counts all those values again. From your expected results it looks like you only want it to count once. Is that right?

Hi Crooza,
Thank you. Now I have amended the formula as below. The 'H' remark is in column 'Ay' only.

Please see the formula and suggest me a proper amendment in it. It's giving perfect output now.

Please Login or Register  to view this content.

Thank you.

This seems to be easier though

Please Login or Register  to view this content.

I think that can be simplified to this...
="F"&
COUNTIF(I8:AM8,"<35")+
COUNTIF(AS8:BF8,"<18")+
COUNTIF(AY8,"H")+
COUNTIF(I8:BF8,"Ab")

Ford,

While his present worksheet has blank columns between the relevant cells in his formula I suspect he's cleaned data out of those 'in between' columns that contains other information relevant to the particular subject. If it is text I can't see it being a problem but I was worried that if there is a number in those columns then that will jigger the result up.

Hi Crooza,
Excellent! Nice approach with a shorten formula.

Hi Ford,
Thank you for a simplest form of formula but there is a data in between the hidden cells. So the formula will give a wrong output.

No matter. Thank you to both of you for suggesting me a amendment in the formula to get the perfect output.