Type X when criteria is match
 

How can I mark X a particular student in a row where grade range of 70-79 is
visible at any of his subjects (i.e Peter with 76 grade in Math)?

Students Math English Science Average Remarks
John 85 90 80 85
Mark 82 85 92 86
Peter 76 85 87 82 X

Thanks,

Rechie

Type X when criteria is match
 

Hi,

Enter this in d2 and drag down

=IF(SUMPRODUCT((B4:D4=70)*(B4:D4<=79))0,"X","")

Mike

"Rechie" wrote:

How can I mark X a particular student in a row where grade range of 70-79 is
visible at any of his subjects (i.e Peter with 76 grade in Math)?

Students Math English Science Average Remarks
John 85 90 80 85
Mark 82 85 92 86
Peter 76 85 87 82 X

Thanks,

Rechie

Type X when criteria is match
 

One way:

=IF((COUNTIF(B2:E2,"="&70)-COUNTIF(B2:E2,"="&80))0,"X","")

Count all the scores bigger than or equal to 70, but subtract the ones that are
too big (bigger than or equal to 80).





Rechie wrote:

How can I mark X a particular student in a row where grade range of 70-79 is
visible at any of his subjects (i.e Peter with 76 grade in Math)?

Students Math English Science Average Remarks
John 85 90 80 85
Mark 82 85 92 86
Peter 76 85 87 82 X

Thanks,

Rechie

--

Dave Peterson

Type X when criteria is match
 

Hi Mike,

Thanks a lot!
It's a big help.

REchie



"Mike H" wrote:


Hi,

Enter this in d2 and drag down

=IF(SUMPRODUCT((B4:D4=70)*(B4:D4<=79))0,"X","")

Mike

"Rechie" wrote:

How can I mark X a particular student in a row where grade range of 70-79 is
visible at any of his subjects (i.e Peter with 76 grade in Math)?

Students Math English Science Average Remarks
John 85 90 80 85
Mark 82 85 92 86
Peter 76 85 87 82 X

Thanks,

Rechie