Countifs query

[Dexir]

Greeting Experts,
I have this sheet, i am using COUNTIFS fuctions and it returning the correct results. i just want it show only 1. if a welder work different places same time. for example K2 showing 5 as per formula, but i want it show only 1.

thanks,

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sr. #	Welder Name	Welder ID	Batch	Root	Hot	Filling	Capping			Root	Hot	Filling	Capping	BW
2	1	Nazrul Islam	W-001	BW	W-001	W-007	W-007	W-007			5	2	0	0
3	2	Ali Azam	W-002	BW	W-001	W-006	W-006	W-006			1	1	0	0
4	3	MD Akter	W-003	BW	W-003	W-007	W-004	W-007			1	1	0	0
5	4	Muhammad Yasir	W-005	BW	W-004	W-006	W-005	W-001			0	1	2	2
6	5	M. Mohsin Ali	W-006	BW	W-001	W-007	W-006	W-002			0	2	5	5
7	6	Louis Thomas	W-007	BW	W-002	W-001	W-006	W-003			1	3	2	2
8	7	Abdul Razak	W-008	BW	W-007	W-002	W-004	W-007			0	0	0	0
9	8	Shahid Shiekh	W-009	BW	W-001	W-003	W-005	W-006			1	0	0	0
10	9	Mohammed Rubel	W-010	BW	W-009	W-004	W-006	W-006			0	0	0	0
11	10	Abdul Rashid	W-011	BW	W-001	W-005	W-006	W-007			1	0	0	0
12	11	Naveed Anjum	W-013	BW	W-011	W-001	W-007	W-008			0	0	0	0
Sheet1

Cell Formulas
Range	Formula
K2:K12	K2	=COUNTIFS($D:$D,$O$1,$E:$E,$C2)
L2:L12	L2	=COUNTIFS($D:$D,$O$1,$F:$F,$C2)
M2:N12	M2	=COUNTIFS($D:$D,$O$1,$G:$G,$C2)

 

[DanteAmor]

Try:

=IF(COUNTIFS($D:$D,$O$1,$E:$E,$C2),1,0)

 

[DanteAmor]

Reviewing the logic of the formulas. Your column N formula should check column H, like this:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sr. #	Welder Name	Welder ID	Batch	Root	Hot	Filling	Capping			Root	Hot	Filling	Capping	BW
2	1	Nazrul Islam	W-001	BW	W-001	W-007	W-007	W-007			1	1	0	1
3	2	Ali Azam	W-002	BW	W-001	W-006	W-006	W-006			1	1	0	1
4	3	MD Akter	W-003	BW	W-003	W-007	W-004	W-007			1	1	0	1
5	4	Muhammad Yasir	W-005	BW	W-004	W-006	W-005	W-001			0	1	1	0
6	5	M. Mohsin Ali	W-006	BW	W-001	W-007	W-006	W-002			0	1	1	1
7	6	Louis Thomas	W-007	BW	W-002	W-001	W-006	W-003			1	1	1	1
8	7	Abdul Razak	W-008	BW	W-007	W-002	W-004	W-007			0	0	0	1
9	8	Shahid Shiekh	W-009	BW	W-001	W-003	W-005	W-006			1	0	0	0
10	9	Mohammed Rubel	W-010	BW	W-009	W-004	W-006	W-006			0	0	0	0
11	10	Abdul Rashid	W-011	BW	W-001	W-005	W-006	W-007			1	0	0	0
12	11	Naveed Anjum	W-013	BW	W-011	W-001	W-007	W-008			0	0	0	0
Hoja2

Cell Formulas
Range	Formula
K2:N12	K2	=IF(COUNTIFS($D:$D,$O$1,E:E,$C2),1,0)

 

[Dexir]

Working Perfect, thanks alot.