Formula with 4 conditions

[amkkhan]

Actually I was making a database file where Student's results are being calculated dynamily.
But I am stuck about making the final pass or fail result when they pass 4 stages of exams in a class. Because for passing or failing for a student in 4 exam stages , there are certain conditions for a student to pas if he is fail in more then one subject.

 

[Alex Blakenburg]

Hi @Alex Blakenburg and @amkkhan
I just modified my formula to suite the range

Does it work? Just wanted to check if I could get it or not

=IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&25)=1,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&30)=2,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40)>2,"Fail","Pass")))

Hello Sufiyan, I don’t believe you can do it in a combined Countifs formula.
3 or more under 40 should generate a Fail.
In your combined version,
1 between 25-40 would pass the 1st test even if there were an additional 1 or more below 25
2 between 30-40 would pass the 2nd test even if there were an additional 1 or more below 25

 

[Sufiyan97]

Hello Alex thanks for the explanation
I still don't understand below points

In your combined version,
1 between 25-40 would pass the 1st test even if there were an additional 1 or more below 25
2 between 30-40 would pass the 2nd test even if there were an additional 1 or more below 25

Can you please explain it in changing the data in below sample

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
3
4
5	Roll No	Name	Exam	T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			Total
6				Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name							Sufiyan	Alex
7				MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	T. Marks	Obtained	T. %age	Grade.
8	1	Student Name	1st BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
9			MT(25%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
10			2nd BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
11			FT(65%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
12		Final Result		100		38.4	100		38.4	100		37.6	100		42.5	100		35.1	100		35.6	100		40	700	268	38.2	E	Fail	Fail
13	2	Student Name	1st BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
14			MT(25%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
15			2nd BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
16			FT(65%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
17		Final Result		100		42.2	100		38.4	100		37.6	100		42.5	100		35.1	100		35.6	100		40	700	271	38.8	E	Fail	Fail
18	3	Student Name	1st BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
19			MT(25%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
20			2nd BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
21			FT(65%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
22		Final Result		100		42.2	100		38.4	100		37.6	100		42.5	100		35.1	100		35.6	100		40	700	271	38.8	E	Fail	Fail
23
24
25
Sheet2

Cell Formulas
Range	Formula
AC12,AC22,AC17	AC12	=IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&25)=1,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&30)=2,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40)>2,"Fail","Pass")))
AD12,AD22,AD17	AD12	=IF(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=0,"Pass",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=1,COUNTIFS($D12:$AB12,"<=25",$D$7:$AB$7,"%age")=0),"Promoted",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=2,COUNTIFS($D12:$AB12,"<=30",$D$7:$AB$7,"%age")=0),"Promoted","Fail")))

 

[Alex Blakenburg]

@Sufiyan97 change any 1 of your < 40 values to a number between 26-29 and tell me if you still get Fail.
eg change F12 to 27

 

[Sufiyan97]

@Sufiyan97 change any 1 of your < 40 values to a number between 26-29 and tell me if you still get Fail.
eg change F12 to 27

Yes still getting Fail

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
4
5	Roll No	Name	Exam	T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			Total
6				Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name							Sufiyan	Alex
7				MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	T. Marks	Obtained	T. %age	Grade.
8	1	Student Name	1st BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
9			MT(25%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
10			2nd BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
11			FT(65%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
12		Final Result		100		27	100		38.4	100		37.6	100		42.5	100		35.1	100		35.6	100		40	700	268	38.2	E	Fail	Fail
Sheet2

Cell Formulas
Range	Formula
AC12	AC12	=IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&25)=1,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&30)=2,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40)>2,"Fail","Pass")))
AD12	AD12	=IF(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=0,"Pass",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=1,COUNTIFS($D12:$AB12,"<=25",$D$7:$AB$7,"%age")=0),"Promoted",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=2,COUNTIFS($D12:$AB12,"<=30",$D$7:$AB$7,"%age")=0),"Promoted","Fail")))

 

[Alex Blakenburg]

Sorry not at my computer and was trying to edit that. You have a lot under 40. Make all your under 40s 20 and then make F12 27

 

[Sufiyan97]

Thanks Alex
Got your point now getting Promoted which is not correct

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
4
5	Roll No	Name	Exam	T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			T.Name			Total
6				Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name			Sub. Name							Sufiyan	Alex
7				MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	MM	OM	%age	T. Marks	Obtained	T. %age	Grade.
8	1	Student Name	1st BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
9			MT(25%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
10			2nd BM(5%)	25	18	3.6	25	21	4.2	25	24	4.8	25	25	5	25	19	3.8	25	14	2.8	25	25	5	175	146	83.4	A+
11			FT(65%)	100	70	17.5	100	60	15	50	28	14	100	65	16.3	100	55	13.8	50	30	15	50	30	15	550	338	61.5	B
12		Final Result		100		27	100		20	100		20	100		20	100		20	100		20	100		20	700	268	38.2	E	Promoted	Fail
Sheet2

Cell Formulas
Range	Formula
AC12	AC12	=IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&25)=1,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40,E12:AB12,">"&30)=2,"Promoted",IF(COUNTIFS($E$7:$AB$7,"%age",E12:AB12,"<"&40)>2,"Fail","Pass")))
AD12	AD12	=IF(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=0,"Pass",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=1,COUNTIFS($D12:$AB12,"<=25",$D$7:$AB$7,"%age")=0),"Promoted",IF(AND(COUNTIFS($D12:$AB12,"<40",$D$7:$AB$7,"%age")=2,COUNTIFS($D12:$AB12,"<=30",$D$7:$AB$7,"%age")=0),"Promoted","Fail")))