# Countifs with one of the columns containing #N/As

#### Greatheights

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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
It would be better to adjust the Vlookup that is returning #N/A.

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

then your countif will work as normal.

OK, I get it now..

Try

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

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.

Glad to help, thanks for the feedback.

Replies
3
Views
181
Replies
1
Views
170
Replies
7
Views
100
Replies
5
Views
163
Replies
4
Views
282

1,196,116
Messages
6,013,563
Members
441,771
Latest member
clamnets

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