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.
 

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
    Capture.PNG
    39.1 KB · Views: 2

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
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
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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! :cool:

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

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016

ADVERTISEMENT

I now see! :cool:

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
Joined
Nov 7, 2019
Messages
53
Office Version
  1. 2016
I now see! :cool:

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

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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