Countif formula

[BASANTH BABU]

I am having a student data to calculate the number of subjects failed by a student. ABSENT is also considered as fail and those who have scored below 50 marks is considered as Fail. The excel sheet designed is as follows:

Roll No Name Subject 1 Subject 2 Subject 3 Subject 4 Subject 5 No. of subjects failed
1001 S-1 59 65 75 80 92 0
1002 S-2 45 ABSENT 60 75 25 3
1003 S-3 75 20 35 60 30 3

and so on..

I need a formula to calculate the number of subjects failed by each student in the last column. Can any one help me how to write the formula?

 

[mrhstn]

Hi

You can try like this...

Book1
	A	B	C	D	E	F	G	H
1	Roll No	Name	Subject 1	Subject 2	Subject 3	Subject 4	Subject 5	Failed
2	1001	S-1	59	65	75	80	92	0
3	1002	S-2	45	ABSENT	60	75	25	3
4	1003	S-3	75	20	35	60	30	3
Sheet5

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(COUNTIF(C2:G2, {"ABSENT","<50"}))

 

[kvsrinivasamurthy]

tRY THIS
in h2, THEN DRAG DOWN

=COUNTIF(C2:G2,"<"&50)+COUNTIF(C2:G2,"ABSENT")

 

[BASANTH BABU]

Hi

You can try like this...

	A	B	C	D	E	F	G	H
Roll No	Name	Subject 1	Subject 2	Subject 3	Subject 4	Subject 5	Failed
S-1
S-2	ABSENT
S-3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1001[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1002[/TD]

[TD="align: right"]45[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1003[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMPRODUCT(COUNTIF(C2:G2, {"ABSENT","<50"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

It works. Thanks a lot.

 

[BASANTH BABU]

It works. Thanks a Lot.