COUNTIFS Formula only using the first value it finds?

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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.
 

Some videos you may like

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
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
Joined
Nov 6, 2013
Messages
664

ADVERTISEMENT

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
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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?
 

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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))),"")
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top