Forum Discussion
formula for multiple conditions
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
5 Replies
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!
@merryashish143merryashish143
As variant
=LET( s, SORT(C2:H2,,,1), IF( SUM(s), IF( MIN(s) > 35, "Pass", "Promoted" ), "Absent" ) )- =LET(g,C2:H2,IF(COUNTIF(g,">=35")=6,"Promoted",IF(COUNTIF(g,"<35")>0,"Passed",IF(SUM(g)=0,"Absent",""))))
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"))
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")
