COUNTIFS Formula only using the first value it finds?

[NGB82KS]

I'm not even sure this is possible, but here is what I have:
Sheet1

	A	B	C	D	E	F
	Name	Class	Code	Code 1	Code 2	Code 3
1	Teacher Name 1	His	101	102	101	103
2	Teacher Name 2	Art	100	102	100	103
3	Teacher Name 3	Eng	202	100	101	108

I have a COUNTIFS formula

=SUM(COUNTIFS(Sheet1!B:B,{"His";"Art";"Eng"},Sheet1!C:C,{"101";"102";"103"})+COUNTIFS(Sheet1!B:B,{"His";"Art";"Eng"},Sheet1!D:D,{"101";"102";"103"})+COUNTIFS(Sheet1!B:B,{"His";"Art";"Eng"},Sheet1!E:E,{"101";"102";"103"})+COUNTIFS(Sheet1!B:B,{"His";"Art";"Eng"},Sheet1!F:F,{"101";"102";"103"})

And it's counting the number of times 101-103 shows up, but what I'm trying to do is if any of those show up, only count the 1st one that shows up in that row and move on, to give me the result of 1.
For instance Row 1, Teacher Name 1 has 101 in column C so it should only count 1 time, but my result is 4 because it's adding all of them.

 

[NGB82KS]

Maybe like this then

Book5
	A	B	C	D	E	F	G
1	Name	Class	Code	Code 1	Code 2	Code 3	Solution
2	Teacher Name 1	His	101	102	101	103	101
3	Teacher Name 2	Art	100	102	100	103
4	Teacher Name 3	Eng	202	100	101	108
Sheet10

Cell Formulas
Range	Formula
G2:G4	G2	=IFERROR(INDEX($C2:$F2,MIN(MATCH({101;102;103},$C2:$F2,0))),"")

Yeah, that would be exactly what I'm looking for. I copied your code and put it in the excel, exactly as is, but only the first row completes, all the other ones stay blank? when I go to the formula and hit enter I get nothing...

 

[Momentman]

Yeah, that would be exactly what I'm looking for. I copied your code and put it in the excel, exactly as is, but only the first row completes, all the other ones stay blank? when I go to the formula and hit enter I get nothing...

it returns blanks because the other rows do not have the code you are searching for, which is 101 to 103. When 101 to 103 is not found, what do you want retturned?

 

[NGB82KS]

it returns blanks because the other rows do not have the code you are searching for, which is 101 to 103. When 101 to 103 is not found, what do you want retturned?

If the code isn't found, nothing returned is fine. In my screenshot, i used the same formula just changed the cell reference but got nothing, i'll try it again and see if i copied it wrong.

 

[Momentman]

If the code isn't found, nothing returned is fine. In my screenshot, i used the same formula just changed the cell reference but got nothing, i'll try it again and see if i copied it wrong.

I now see!

try this modification

ForMRexcel.xlsm
	A	B	C	D	E	F	G
1	Name	Class	Code	Code 1	Code 2	Code 3	Solution
2	Teacher Name 1	His	101	102	101	103	101
3	Teacher Name 2	Art	100	102	100	103	102
4	Teacher Name 3	Eng	202	100	101	108	101
Sheet10

Cell Formulas
Range	Formula
G2:G4	G2	=IFERROR(INDEX($C2:$E2,AGGREGATE(15,6,MATCH({101;102;103},$C2:$F2,0),1)),"")

 

[NGB82KS]

I now see!

try this modification

ForMRexcel.xlsm
	A	B	C	D	E	F	G
1	Name	Class	Code	Code 1	Code 2	Code 3	Solution
2	Teacher Name 1	His	101	102	101	103	101
3	Teacher Name 2	Art	100	102	100	103	102
4	Teacher Name 3	Eng	202	100	101	108	101
Sheet10

Cell Formulas
Range	Formula
G2:G4	G2	=IFERROR(INDEX($C2:$E2,AGGREGATE(15,6,MATCH({101;102;103},$C2:$F2,0),1)),"")

That was it....!!!!!! THANK YOU THANK YOU!

 

[NGB82KS]

I now see!

try this modification

ForMRexcel.xlsm
	A	B	C	D	E	F	G
1	Name	Class	Code	Code 1	Code 2	Code 3	Solution
2	Teacher Name 1	His	101	102	101	103	101
3	Teacher Name 2	Art	100	102	100	103	102
4	Teacher Name 3	Eng	202	100	101	108	101
Sheet10

Cell Formulas
Range	Formula
G2:G4	G2	=IFERROR(INDEX($C2:$E2,AGGREGATE(15,6,MATCH({101;102;103},$C2:$F2,0),1)),"")

Can i enclose a letternumber code within the { }? aside from the 101-112, i have a few codes that start with a 'C100-C108' and 'O200-O224'?

 

[NGB82KS]

Can i enclose a letternumber code within the { }? aside from the 101-112, i have a few codes that start with a 'C100-C108' and 'O200-O224'?

Nevermind i can add "" around each item with alpha characters and its working... thanks again!