Formula error

Dear friends,
In cell ‘BE8’ I want to insert this formula but it is giving error ‘missing parentheses. It’s a result sheet and the terms are as follows:

If the student is fail in 1 subject (<35) then the remark should be ‘F1’
If the student is fail in 2 subject (<35) then the remark should be ‘F2’
If the student is fail in 3 subject (<35) then the remark should be ‘F3’
If the student is fail in 4 subject (<35) then the remark should be ‘F4’
If the student is fail in 5 subject (<35) then the remark should be ‘F5’
If the student is fail in 6 subject (<35) then the remark should be ‘F6’

The formula is:

=countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=1, "F1", countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=2, "F2", countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=3, "F3", countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=4, "F4", countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=5, "F5", countif(IF(AND(j8 < 35, n8 < 35, r8 < 35, v8 < 35, z8 < 35, ad8 < 35=6, "F6", "P all")

Please suggest me a correction.

Thanking you.

Sincerely,

Mukesh

Hi,
Maybe you can use this formula:

Formula:

="F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2))

try
="F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35")

=IF( (COUNTIF(J8,">=35")+COUNTIF(N8,">=35")+COUNTIF(R8,">=35")+COUNTIF(V8,">=35")+COUNTIF(Z8,">=35")+COUNTIF(AD8,">=35"))=6, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))

OR

IF( (COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))=0, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))

=IF(SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all")
or maybe
=CHOOSE(1+SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all","f1","f2","f3","f4","f5","f6")

does martindwilson version also not work for blank cells

its going to get quite a lengthy formula to avoid blanks in 2003
is that version of excel you are using ?

this condition of F0 would be if ALL cells where blank

=IF( AND(J8="", N8="",R8="",V8="",Z8="",AD8="" ), "",IF( (COUNTIF(J8,">=35")+COUNTIF(N8,">=35")+COUNTIF(R8,">=35")+COUNTIF(V8,">=35")+COUNTIF(Z8,">=35")+COUNTIF(AD8,">=35"))=6, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35")))

=IF(SUMPRODUCT((J8:AD8="")*(MOD(COLUMN(J8:AD8),4)=2))=6,"",CHOOSE(1+SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all","f1","f2","f3","f4","f5","f6"))

works fine for me see attached

Originally Posted by mukeshbaviskar:3651677

Hi etaf,
Thank you for formula. It's working fine but only one correction is required. If the student is passed in all six subjects then the remark should be 'P all'. Now it's giving 'F0' which I don't want.

Thank you.

Sincerely,

Mukesh

Hi,

Formula:

 =SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all")

that still gives f6 for all blanks not blank ,the op added an extra requirement "If the cells are blank then the remark column should be blank."
so maybe
=IF(SUMPRODUCT((J8:AD8="")*(MOD(COLUMN(J8:AD8),4)=2))=6,"",SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all"))
or even slightly shorter
=IF(COUNT(J8,N8,R8,V8,Z8,AD8)=0,"",SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all")) a bit shorter still
=IF(COUNT(J8,N8,R8,V8,Z8,AD8),SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all"),"")