Countifs with one of the columns containing #N/As

[Greatheights]

On sheet "Data2" Column A has the name of the Underwriter that saw a case (with repeated values), and column J has the name of the Doctor that the case was referred to. Many cases don't get referred to doctors, so those rows have a #N/A instead of the Doctor's name. (the Doctors names are entered with a vlookup from another sheet.)

On sheet "Charts", I've got a list of the Underwriters names (also in column A, starting in row28), and the total count of cases they saw. I'm tring to get a count of the cases those underwriters saw that were also referred to a doctor, so that I can ultimately calculate the ratio.

I'm a little shaky on how to use the IsNA or IsError functions. Also new to array formulas.

This is what I tried (formula is in sheet "Charts"):

<CODE>{=IF(--(ISNA(Data2!J:J)),,(--COUNTIF(Data2!A:A,$A28)))}</CODE>

This ended up giving me the same result as my formula to just count the number of cases the underwriter saw:

<CODE>=COUNTIF(Data2!$A:$A, $A28)</CODE>

That doesn't make any sense to me.

I also tried this, but Excel wouldn't even accept it as a valid function:

<CODE>{=COUNTIFS(Data2!$A:$A, $A28, IsNA(Data2!$J:$J), False))}</CODE>

Am I even taking the right approach? Any help would be great. Let me know if I need to clarify anything.

 

[Jonmo1]

It would be better to adjust the Vlookup that is returning #N/A.

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

then your countif will work as normal.

 

[Jonmo1]

OK, I get it now..

Try

=COUNTIFS(Data2!A:A,$A28,Data2!J:J,"<>#N/A")

 

[Greatheights]

Oh man, that was deceptively simple. I didn't realize you could just put #N/A into a formula like that. This worked perfectly, thanks.

 

[Jonmo1]

Glad to help, thanks for the feedback.