COUNTIFS QUERY

[Dexir]

Hi All,
i have this sheet, im using COUNTIFS, i want to get data against JOINT NUMBER (A1). if a Welder work on any Joint Number on any place (Root, Hot, Filling, Capping), formula just count 1 as you can see in formula with IF condition. i am not getting the results. What am i missing ? and i have large number of JOINT NUMBERS, is there any easy way to count ?
I just want if any of Welder touch any JOINT NUMBER it just count on next to his ID.

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Joint Number	Root		Hot		Filling		Capping
2		Right	Left	Right	Left	Right	Left	Right	Left		Sr. #	Welder ID	FW-0021M	FW-0022M	FW-0023M	FW-0024M	FW-0025M
3	FW-0021M	W-001	W-017	W-001	W-001	W-025	W-001	W-001	W-023		1	W-001	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
4	FW-0022M	W-015	W-017	W-015	W-017	W-024	W-016	W-024	W-016		2	W-002
5	FW-0023M	W-015	W-017	W-015	W-017	W-025	W-023	W-001	W-023		3	W-003
6	FW-0024M	W-015	W-001	W-001	W-017	W-001	W-001	W-001	W-016		4	W-005
7	FW-0025M	W-015	W-017	W-015	W-017	W-025	W-023	W-025	W-023		5	W-006
8	FW-0026M	W-015	W-017	W-015	W-017	W-024	W-016	W-024	W-016		6	W-007
9	FW-0027M	W-015	W-017	W-015	W-017	W-025	W-023	W-025	W-023		7	W-008
10	FW-0028M	W-015	W-017	W-015	W-017	W-024	W-016	W-024	W-016		8	W-009
Sheet1

Cell Formulas
Range	Formula
M3:Q3	M3	=IF(COUNTIFS($A$3:$A$10,M$2,$B3:$I3,$L3),1,0)

 

[SunnyAlv]

Try this simple Formula

copy this formula on Cell "M3" and fill to all rows

=SUMPRODUCT(($A$3:$A$10=M$2)*($B$3:$I$10=$L3))

or if u want all result is "1"

=IFERROR(SUMPRODUCT(($A$3:$A$10=M$2)*($B$3:$I$10=$L3))/SUMPRODUCT(($A$3:$A$10=M$2)*($B$3:$I$10=$L3)),"")

 

[Dexir]

Thanks sir, working good. But I just want if any of Welder touch any JOINT NUMBER it just count on next to his ID. i want it count total on M3 column. because JOINT NUMBERS is too lengthy and i can't add them on 3 (row) in sequance.
for example,

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Joint Number	Root		Hot		Filling		Capping
2		Right	Left	Right	Left	Right	Left	Right	Left		Sr. #	Welder ID	Total
3	FW-0021M	W-001		W-001		W-001					1	W-001	4
4	FW-0022M										2	W-002
5	FW-0023M		W-001			W-001			W-001		3	W-003
6	FW-0024M										4	W-005
7	FW-0025M										5	W-006
8	FW-0026M			W-001		W-001					6	W-007
9	FW-0027M										7	W-008
10	FW-0028M			W-001							8	W-009
Sheet1

 

[SunnyAlv]

Try this @Dexir

On Cell "M3"

=SUM(--(MMULT(--(B3:I10=L3),TRANSPOSE(COLUMN(B3:I10)))>0))

 

[Dexir]

Thanks alot, working good.

 

[SunnyAlv]

Thanks alot, working good.

no problem