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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Momentman

##### Well-known Member
If I understand you currently, then maybe
Book5
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Count
2Teacher Name 1His1011021011031
3Teacher Name 2Art1001021001031
4Teacher Name 3Eng2021001011081
Test
Cell Formulas
RangeFormula
G2:G4G2=1*(SUMPRODUCT((\$C2:\$F2>=101)*(\$C2:\$F2<=103))>0)

#### NGB82KS

##### Board Regular
If I understand you currently, then maybe
Book5
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Count
2Teacher Name 1His1011021011031
3Teacher Name 2Art1001021001031
4Teacher Name 3Eng2021001011081
Test
Cell Formulas
RangeFormula
G2:G4G2=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

##### Board Regular
If I understand you currently, then maybe
Book5
ABCDEFG
1NameClassCodeCode 1Code 2Code 3Count
2Teacher Name 1His1011021011031
3Teacher Name 2Art1001021001031
4Teacher Name 3Eng2021001011081
Test
Cell Formulas
RangeFormula
G2:G4G2=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

##### Well-known Member

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

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

##### Well-known Member

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

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

#### NGB82KS

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

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

Replies
5
Views
46
Replies
6
Views
77
Replies
4
Views
62
Replies
4
Views
149
Replies
11
Views
223

1,127,314
Messages
5,623,945
Members
416,001
Latest member
teabag

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