You can use COUNTIF for the nested if's usind AND

One way:

=IF(OR((ISNUMBER(SEARCH("e",E7&G7&I7&K7))),(M7&O7&Q7="eee"),(S7&U7="ee")),
  "Failed","Passed")

Hello,

I suggest to use:

=pass_or_fail("MUSTNOTFAIL",E1,G1,I1,K1,"MUSTPASS",M1,O1,Q1,"MUSTPASS",S1,U1)

You will have to put the code shown below into a VBA module (push ALT +
F9, insert a module and paste the code shown below):

Option Explicit
Const spasschar As String = "ABCD"
Const sfailchar As String = "E"
Function pass_or_fail(ParamArray v() As Variant) As Boolean
'Call with
=pass_or_fail("MUSTNOTFAIL",E1,G1,I1,K1,"MUSTPASS",M1,O1,Q1,"MUSTPASS",S1,U1)
'to solve newsgroup question shown below.
'From:  Zainuddin Zakaria - view profile
'Date:  Sun, Sep 24 2006 4:23 pm
'Email:   "Zainuddin Zakaria" <s...@tm.net.my>
'Groups:   microsoft.public.Excel
'
'
'Please help me with a formula for my marksheet.
'
'The exam grading is A, B, C, D and E.  E is a failure
'
'
'To pass the overall exam, a student must :-
'
'
'Must NOT FAIL in any 4 subjects from columns E7, G7, I7 and K7
'and MUST PASS ONE subject from column M7, O7, or Q7
'and MUST PASS ONE subject from column S7, or U7
'
'
'What is the best formula  for this?
Dim i As Long, j As Long
Dim bpass As Boolean
Dim r As Range
Dim vi As Variant
Dim s As String

s = "Error"
For Each vi In v
   Select Case TypeName(vi)
   Case "String"
       If s = "MUSTPASS" And Not bpass Then
           pass_or_fail = False
           Exit Function
       End If
       s = vi
       If s <> "MUSTPASS" And s <> "MUSTNOTFAIL" Then
           pass_or_fail = CVErr(xlErrValue)
           Exit Function
       End If
       bpass = False
   Case "Range"
       If s = "MUSTNOTFAIL" Then
           For Each r In vi
               If InStr(sfailchar, r) > 0 And Len(r) > 0 Then
                   pass_or_fail = False
                   Exit Function
               End If
           Next r
       ElseIf s = "MUSTPASS" Then
           For Each r In vi
               If InStr(spasschar, r) > 0 And Len(r) > 0 Then
                   bpass = True
               End If
           Next r
       Else
           pass_or_fail = CVErr(xlErrValue)
           Exit Function
       End If
   Case Else
       pass_or_fail = CVErr(xlErrRef)
       Exit Function
   End Select
Next vi
If s = "MUSTPASS" And Not bpass Then
   pass_or_fail = False
   Exit Function
End If
pass_or_fail = True

End Function

Dearest all,

Thank you so much for coming out with great suggestions.
You guys are really experts in this.
My problem is solved,

Thanks, Don Guilet, Dave Peterson, Max and Bplumhoff.

Zainuddin Zakaria