formula for multiple conditions

[merryashish143]

I have 6 columns representing 6 subjects

7th column is of Total marks of all the 6 subjects whereas the 8th column is of Result

The Result column should display three outputs based on the following conditions:

1) if marks in "all" 6 subjects greater than 35% :PASS

2) If marks in any of six subjects is less than 35% : PROMOTED

3) if marks in all 6 subjects is 0 : ABSENT 

[#2]

@merryashish143 I believe this formula does exactly what you ask for, though I'm not sure what want to return if not all 6 marks are blank. This one will return "Promoted" even when you only enter one mark.

=IF(COUNT(C2:H2),IF(SUM(--(C2:H2>={35,35,35,35,35,35}))=6,"Pass",IF(SUM(--(C2:H2<{35,35,35,35,35,35}))>0,"Promoted")),"Absent")

[#3]

Hi @merryashish143 

Try this formula

=IF(AND(C2=0,D2=0,E2=0,F2=0,G2=0,H2=0),"Absent",IF(AND(C2>35,D2>35,E2>35,F2>35,G2>35,H2>35),"Pass","Promoted"))

[#4]

=LET(g,C2:H2,IF(COUNTIF(g,">=35")=6,"Promoted",IF(COUNTIF(g,"<35")>0,"Passed",IF(SUM(g)=0,"Absent",""))))

[#5]

@merryashish143 

As variant

=LET( s, SORT(C2:H2,,,1), IF( SUM(s), IF( MIN(s) > 35, "Pass", "Promoted" ), "Absent" ) )

[#6]

Here is another formula that seems to do the job:

=IFS(MIN(D7:I7)>=35,"Pass",SUM(D7:I7)=0,"Absent",MIN(D7:I7)<35,"Promoted")

Cool thing about Excel - there are all kinds of ways to do things!

@merryashish143@merryashish143