Nesting CountIF statements or Using IF and Countif

[jnelson1010]

I am trying to get excel look for a name in one column and then count the number of cells that have a certain word.

Currently I am trying: =COUNTIFS($F$1:$P$138,$S$4,$F$1:$P$138,X5) but all I get is a #VALUE.

I have tried it as an array formula as well. I don't know why it isn't working!!! GAH!

I am doing this for work and have to go through multiple worksheets performing the same type of review and I just can't make it work.

 

[jnelson1010]

Please and thank you!

 

[Jonmo1]

So what is it meant to do?

Are you trying to find the column by the value of S4 ?
So find S4 in F1:P1, and do the countif based on that column ?

Try
=COUNTIF(INDEX($F$1:$P$138,0,MATCH($S$4,$F$1:$P$1,0)),X5)

 

[Aladin Akyurek]

Would you care to post a scaled-down sample along with the expected count?

 

[jnelson1010]

Here is a sample of my data. I need to be able to count which mistakes each officer code is making but they are listed in multiple columns. I need to be able to provide a list of each error an officer makes. I have a list of each officer code and the potential errors, and then this is my data. I don't know if I am making any sense at all, but any help would be appreciated. I will post another reply with the criteria I am looking for.

[TABLE="width: 1166"]
<tbody>[TR]
[TD]MSC[/TD]
[TD]ERROR1[/TD]
[TD]ERROR2[/TD]
[TD]ERROR3[/TD]
[TD]ERROR4[/TD]
[TD]ERROR5[/TD]
[TD]ERROR6[/TD]
[TD]ERROR7[/TD]
[/TR]
[TR]
[TD]AW[/TD]
[TD]GAP INSURANCE ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AW[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD]INSURANCE DEC PAGE MISSING/INCORRECT[/TD]
[TD]GAP INSURANCE ERROR[/TD]
[TD]EXTENDED WARRANTY ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]RATE INCORRECT [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]COLLATERAL INFO INCORRECT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]RATE INCORRECT [/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD]CREDIT LIFE/DISABILITY ERROR[/TD]
[TD]ACH/AUTO PAY NOT SET UP[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD]CREDIT LIFE/DISABILITY ERROR[/TD]
[TD]SUPPLEMENTAL INS FORM ERROR[/TD]
[TD]LOAN NAME INCORRECT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]UW STIPS NOT CLEARED[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]TITLE APP NOT SIGNED/DATED CORRECTLY[/TD]
[TD]INSURANCE DEC PAGE MISSING/INCORRECT[/TD]
[TD]GAP INSURANCE ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BM[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BY[/TD]
[TD]TITLE FEE INCORRECT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BY[/TD]
[TD]RATE INCORRECT [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CG[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CG[/TD]
[TD]RATE INCORRECT [/TD]
[TD]ACH/AUTO PAY NOT SET UP[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CG[/TD]
[TD]UW STIPS NOT CLEARED[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CG[/TD]
[TD]CREDIT LIFE/DISABILITY ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CG[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD]BILL OF SALE/PURCH AGREEMENT MISSING[/TD]
[TD]INSURANCE DEC PAGE MISSING/INCORRECT[/TD]
[TD]GAP INSURANCE ERROR[/TD]
[TD]EXTENDED WARRANTY ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CK[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CK[/TD]
[TD]UW STIPS NOT CLEARED[/TD]
[TD]DOCUMENT ERROR[/TD]
[TD]BILL OF SALE/PURCH AGREEMENT MISSING[/TD]
[TD]SUPPLEMENTAL INS FORM ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CK[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD]SUPPLEMENTAL INS FORM ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CK[/TD]
[TD]MISSING DOCUMENTS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CK[/TD]
[TD]SUPPLEMENTAL INS FORM ERROR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[jnelson1010]

Re: Nesting CountIF statements or Using IF and Countif-CRITERIA

[TABLE="width: 377"]
<tbody>[TR]
[TD]POTENTIAL ERRORS [/TD]
[TD]OFFICER CODES[/TD]
[/TR]
[TR]
[TD]LOAN AMOUNT INCORRECT[/TD]
[TD]AW[/TD]
[/TR]
[TR]
[TD]RATE INCORRECT [/TD]
[TD]BY[/TD]
[/TR]
[TR]
[TD]TERM INCORRECT[/TD]
[TD]BM[/TD]
[/TR]
[TR]
[TD]UNAUTHORIZED PRODUCT TERM EXTENSION [/TD]
[TD]CH[/TD]
[/TR]
[TR]
[TD]UW STIPS NOT CLEARED[/TD]
[TD]CG[/TD]
[/TR]
[TR]
[TD]MISSING DOCUMENTS[/TD]
[TD]CK[/TD]
[/TR]
[TR]
[TD]DOCUMENT ERROR[/TD]
[TD]CW[/TD]
[/TR]
[TR]
[TD]LOAN AGREEMENT ITEMIZATION ERROR[/TD]
[TD]JG[/TD]
[/TR]
[TR]
[TD]DOCS NOT SIGNED/DATED CORRECTLY[/TD]
[TD]JP[/TD]
[/TR]
[TR]
[TD]LOAN NAME INCORRECT[/TD]
[TD]JH[/TD]
[/TR]
[TR]
[TD]COLLATERAL INFO INCORRECT[/TD]
[TD]JN[/TD]
[/TR]
[TR]
[TD]VIN INCORRECT[/TD]
[TD]MJ[/TD]
[/TR]
[TR]
[TD]TITLE APP NOT SIGNED/DATED CORRECTLY[/TD]
[TD]TT[/TD]
[/TR]
[TR]
[TD]TITLE FEE INCORRECT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MISSING COPY OF TITLE [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BILL OF SALE/PURCH AGREEMENT MISSING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INSURANCE DEC PAGE MISSING/INCORRECT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUPPLEMENTAL INS FORM ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GAP INSURANCE ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXTENDED WARRANTY ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CREDIT LIFE/DISABILITY ERROR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACH/AUTO PAY NOT SET UP[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

Re: Nesting CountIF statements or Using IF and Countif-CRITERIA

The exhibit of #5 seems to be the input. If so, it is not clear what the exhibit of post #6 means and what the desired output must be. Care to clarify?