Count Rows based multiple condition in column

[B_Siva]

Hi,
I would like to count number of day a employee work >4:50 pm on weekday and >11:50 am on weekend.
Count only the out time.
Below formula does give me the right answer.
COUNTIFS(C6:P6,">6:45 PM",C4:P4,"<>Sat",C4:P4,"<>Sun",C5:P5,"=Out"),0)

Pls help.

 

[B_Siva]

*Below formula does not give give me the right answer*
COUNT(COUNTIFS(C6:P6,">6:45 PM",C5:P5,"=Out",C4:P4,"<>Sat",C4:P4,"<>Sun"),0)

 

[Peter_SSs]

Can you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you give us the sample data and expected results withXL2BB? Providing sample data that way makes it easier for helpers by not having to manually type out sample data to test with. Also, your image is quite hard to read anyway.

 

[B_Siva]

Employee Number of day OT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3	Employee ID	Employee Name	1-Jan-21		2-Jan-21		3-Jan-21		4-Jan-21		5-Jan-21		6-Jan-21		7-Jan-21		No of Weekday OT (>6:45 pm)	No of Weekend OT (>11:50 am)
4			Mon		Tue		Wed		Thu		Fri		Sat		Sun
5			In	Out	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out
6	123456	ABCD	7:25 AM	7:00 PM	7:25 AM	7:00 PM	7:25 AM	5:00 PM	7:25 AM	7:00 PM	7:25 AM	5:00 PM	7:25 AM	11:00 AM	7:25 AM	5:00 PM	2	2
7																	correct answer 3	correct answer 1
8
Sheet1

Cell Formulas
Range	Formula
Q6	Q6	=COUNT(COUNTIFS($C6:$P6,">6:45 PM",$C$5:$P$5,"=Out",$C$4:$P$4,"<>Sat",$C$4:$P$4,"<>Sun"),0)
R6	R6	=COUNT(COUNTIFS($C6:$P6,">11:50 AM",$C$5:$P$5,"=Out",$C$4:$P$4,"Sat",$C$4:$P$4,"Sun"),0)

 

[KevCarter]

The issues I tried and seem to help. Please wait for one of the Experts to confirm.

Merged cells are evil when used in formulas.
Use SUM instead of COUNT for the final count
In countifs using {"Sat","Sun"} tells excel you want either option.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3	Employee ID	Employee Name	1/1/21		1/2/21		1/3/21		1/4/21		1/5/21		1/6/21		1/7/21		No of Weekday OT (>6:45 pm)	No of Weekend OT (>11:50 am)
4			Mon	Mon	Tue	Tue	Wed	Wed	Thu	Thu	Fri	Fri	Sat	Sat	Sun	Sun
5			In	Out	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out
6	123456	ABCD	7:25 AM	7:00 PM	7:25 AM	7:00 PM	7:25 AM	5:00 PM	7:25 AM	7:00 PM	7:25 AM	5:00 PM	7:25 AM	11:00 AM	7:25 AM	5:00 PM	3	1
7																	correct answer 3	correct answer 1
Sheet1

Cell Formulas
Range	Formula
Q6	Q6	=SUM(COUNTIFS($C6:$P6,">6:45 PM",$C$5:$P$5,"Out",$C$4:$P$4,"<>Sat",$C$4:$P$4,"<>Sun"))
R6	R6	=SUM(COUNTIFS($C6:$P6,">11:50 AM",$C$5:$P$5,"Out",$C$4:$P$4,{"Sat","Sun"}))

 

[Peter_SSs]

It is fine to keep the merged cells if you want. Notice the offset ranges.

21 01 04.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3	1-Jan-21		2-Jan-21		3-Jan-21		4-Jan-21		5-Jan-21		6-Jan-21		7-Jan-21		No of Weekday OT (>6:45 pm)	No of Weekend OT (>11:50 am)
4	Mon		Tue		Wed		Thu		Fri		Sat		Sun
5	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out	In	Out
6	7:25:00 AM	7:00:00 PM	7:25:00 AM	7:00:00 PM	7:25:00 AM	5:00:00 PM	7:25:00 AM	7:00:00 PM	7:25:00 AM	5:00:00 PM	7:25:00 AM	11:00:00 AM	7:25:00 AM	5:00:00 PM	3	1
Counting

Cell Formulas
Range	Formula
Q6	Q6	=COUNTIFS(D6:P6,">6:45 PM",D$5:P$5,"=Out",C$4:O$4,"<>S*")
R6	R6	=COUNTIFS(D6:P6,">11:50 AM",D$5:P$5,"=Out",C$4:O$4,"S*")

 

[B_Siva]

Peter Thanks
It is working. Thanks alot.

 

[Peter_SSs]

Peter Thanks
It is working. Thanks alot.

You're welcome. Thanks for the follow-up.