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.

 

[Momentman]

If I understand you currently, then maybe

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

Cell Formulas
Range	Formula
G2:G4	G2	=1*(SUMPRODUCT(($C2:$F2>=101)*($C2:$F2<=103))>0)

 

[NGB82KS]

If I understand you currently, then maybe

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

Cell Formulas
Range	Formula
G2:G4	G2	=1*(SUMPRODUCT(($C2:$F2>=101)*($C2:$F2<=103))>0)

I'm going to test this on my sheet as soon as I'm done with this staff meeting and let you know.

 

[NGB82KS]

If I understand you currently, then maybe

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

Cell Formulas
Range	Formula
G2:G4	G2	=1*(SUMPRODUCT(($C2:$F2>=101)*($C2:$F2<=103))>0)

Momentman, so the codes i used were an example, I have some that are listed like Y0C4 and O0CY, can I take your formula and use a range like "101";"102";"103";"O0CY"? And can I still have it apply to the filter of their specific classes?

 

[kvsrinivasamurthy]

Try this in G2

Only 101 counted
=0+(Countif(C2:F2,101)>0)

If all are counted
=SUM(COUNTIF(C2:F2,{101,102,103}))

 

[NGB82KS]

Try this in G2

Only 101 counted
=0+(Countif(C2:F2,101)>0)

If all are counted
=SUM(COUNTIF(C2:F2,{101,102,103}))

So, with the 1st one, it's only looking for 101, but I need the range because there are 6 codes in total I have to look for so on my dashboard i can sum them.
So it should look at the whole row, if it finds any of the codes, counts the 1st code and then stops counting that row and puts the code it found in the new column. does that make sense? i feel like I'm not explaining this well...

 

[Momentman]

It

So, with the 1st one, it's only looking for 101, but I need the range because there are 6 codes in total I have to look for so on my dashboard i can sum them.
So it should look at the whole row, if it finds any of the codes, counts the 1st code and then stops counting that row and puts the code it found in the new column. does that make sense? i feel like I'm not explaining this well...

it may be good to add the sample data with a result column for each row, so we are sure what the output is. So is it returning a COUNT or the code itself?

 

[kvsrinivasamurthy]

Try this for each line
If all are counted
=SUM(1*(COUNTIF(C2:F2,{101,102,103})>0))

 

[NGB82KS]

It

it may be good to add the sample data with a result column for each row, so we are sure what the output is. So is it returning a COUNT or the code itself?

Ok, so I definitely messed this up but didn't think about adding another column to do the calculations on that sheet. The formula I guess I need after looking into it is an INDEX Match search function to look at the whole row and return the result in the last column of the exact code I need so I can then use that on my dashboard.

 

[Momentman]

Ok, so I definitely messed this up but didn't think about adding another column to do the calculations on that sheet. The formula I guess I need after looking into it is an INDEX Match search function to look at the whole row and return the result in the last column of the exact code I need so I can then use that on my dashboard.

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))),"")