Avoid Double counting with COUNTIF

[Tigerexcel]

Book5
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N	4	2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P	4	3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF	3	3		*N
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=SUM(COUNTIF(A2:D2,$H$2:$H$4))

Trying to get this formula to count relevant cells according to the criteria set in column H. I've bolded the cells that should be counted. My formula appears to work in only one of the 3 ranges ie row 4. I suspect there's double counting going on. Can my formula be tweaked to work right?

 

[Michael M]

Shouldn't the ABC* be 5 not 2 ??

Book1
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N	5	2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P	3	3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF	3	3		*N
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIF($A$2:$D$4,H2)

 

[Tigerexcel]

Shouldn't the ABC* be 5 not 2 ??

Book1
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N	5	2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P	3	3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF	3	3		*N
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIF($A$2:$D$4,H2)

Each row is a separate record, all 3 criteria apply to each record.

 

[ybr_15]

I'm just trying (not an expert), is it like this?

Book1
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N		2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P		3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF		3		*N
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(IF(SUM(COUNTIF(A2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(B2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(C2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(D2,{"ABC1*";"*DEF";"*N"}))>0,1,0))

 

[Tigerexcel]

I'm just trying (not an expert), is it like this?

Book1
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N		2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P		3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF		3		*N
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(IF(SUM(COUNTIF(A2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(B2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(C2,{"ABC1*";"*DEF";"*N"}))>0,1,0),IF(SUM(COUNTIF(D2,{"ABC1*";"*DEF";"*N"}))>0,1,0))

It would be preferable if the criteria wasn't hard-coded because the criteria will change depending on the type of analysis.

 

[hnsd24_CN]

test.xlsm
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N	2	2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P	3	3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF	3	3		*N
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=SUMPRODUCT(--(MMULT({1,1,1},IFERROR(SEARCH($H$2:$H$4,A2:D2),0))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tigerexcel]

test.xlsm
	A	B	C	D	E	F	G	H
1						Should return		Criteria
2	ABC1000 N	ABC2000 P	ABC2100 C	ABC1020 N	2	2		ABC1*
3	ABC1000 N	ABC2000 P	ABC2100 DEF	ABC1020 P	3	3		*DEF
4	ABC1000 D	ABC2000 P	ABC2100 DEF	ABC2020 DEF	3	3		*N
Sheet4

Cell Formulas
Range	Formula
E2:E4	E2	=SUMPRODUCT(--(MMULT({1,1,1},IFERROR(SEARCH($H$2:$H$4,A2:D2),0))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks hnds, that works. Haven't used MMULT before but it appears to be an effective function in this case. I guess that COUNTIF was not the most appropriate function to use here.
A follow-up question, what if I wanted to exclude all N records, I tried <>N but it didn't change the count.