# 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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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

ADVERTISEMENT

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
47
Replies
6
Views
81
Replies
4
Views
63
Replies
4
Views
153
Replies
11
Views
225

Threads
1,127,822
Messages
5,627,096
Members
416,220
Latest member
Sykotik7S

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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