# COUNTIFS Formula only using the first value it finds?

#### NGB82KS

##### Board Regular
I'm not even sure this is possible, but here is what I have:
Sheet1
ABCDEF
NameClassCodeCode 1Code 2Code 3
1Teacher Name 1His101102101103
2Teacher Name 2Art100102100103
3Teacher Name 3Eng202100101108

I have a COUNTIFS formula
Excel 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

##### Board Regular
Maybe like this then
Book5
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Solution
2Teacher Name 1His101102101103101
3Teacher Name 2Art100102100103
4Teacher Name 3Eng202100101108
Sheet10
Cell Formulas
RangeFormula
G2:G4G2=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...

#### Attachments

• Capture.PNG
39.1 KB · Views: 2

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Momentman

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Solution
2Teacher Name 1His101102101103101
3Teacher Name 2Art100102100103102
4Teacher Name 3Eng202100101108101
Sheet10
Cell Formulas
RangeFormula
G2:G4G2=IFERROR(INDEX(\$C2:\$E2,AGGREGATE(15,6,MATCH({101;102;103},\$C2:\$F2,0),1)),"")

#### NGB82KS

##### Board Regular

I now see!

try this modification
ForMRexcel.xlsm
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Solution
2Teacher Name 1His101102101103101
3Teacher Name 2Art100102100103102
4Teacher Name 3Eng202100101108101
Sheet10
Cell Formulas
RangeFormula
G2:G4G2=IFERROR(INDEX(\$C2:\$E2,AGGREGATE(15,6,MATCH({101;102;103},\$C2:\$F2,0),1)),"")
That was it....!!!!!! THANK YOU THANK YOU!

#### NGB82KS

##### Board Regular
I now see!

try this modification
ForMRexcel.xlsm
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Solution
2Teacher Name 1His101102101103101
3Teacher Name 2Art100102100103102
4Teacher Name 3Eng202100101108101
Sheet10
Cell Formulas
RangeFormula
G2:G4G2=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

##### Board Regular
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!

Replies
5
Views
49
Replies
6
Views
116
Replies
4
Views
67
Replies
4
Views
157
Replies
11
Views
233

1,129,570
Messages
5,637,114
Members
416,959
Latest member
Mohzein

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back