# Countif problem with N/A

#### winrz

##### Board Regular
I have this formula which looks at the dates in column N and compares it to another, column M. It does a good job at telling me how many entries there are for Jan, how many for Feb, etc..

what I cant figure out is as soon as someone puts a "N/A" into the date column N, this formula chokes.. I cannot figure out how or where to put an IF statement to look for the "N/A" and disregard it.

=SUMPRODUCT((MONTH(\$N\$17:\$N\$25)=MONTH(M17&0))*(YEAR(\$N\$17:\$N\$25)=\$J\$2))

N = column input for month and year
M = column that houses the 12 months to compare against
\$J\$2 = 2004

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Joe4

##### MrExcel MVP, Junior Admin
If you do a Search and Replace, replacing "N/A" with nothing, the formulas should work again.

BTW, can you also delete your other duplicate post of this question? You should only post your question once.

#### winrz

##### Board Regular
I will delete the dupe post - network issue..

I can delete the N/A's but for future reference, how would i get around that entry (whether it be N/A or some other garbage)?

#### Joe4

##### MrExcel MVP, Junior Admin
You can use an event procedure macro to ensure they enter nothing other than dates in a particular column.

For example, if you only want to allow date entries in column A, right click on the tab name at the bottom of the sheet, select View Code, and paste this code in the blank window:

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Len(Target) > 0 Then
If Not IsDate(Target) Then
Target.ClearContents
MsgBox "You cannot enter anything other than dates in this column"
End If
End If

End Sub``````

Now try to enter something other than a date in column A and you'll see what happens.

#### winrz

##### Board Regular
1. I do not know how to delet the dupe post. any ideas.. n

2. Thanks for the macro. But I do want the user to be able to post anything in that column. I want to be able to compare dates, which most of the entries should be, but still want to be able to disregard the 'garbage' if you will.

3. In this instance I am unable to have a macro running -its a shared file on the server, and many may not enable the macro to run. Keeping it simple.

#### Joe4

##### MrExcel MVP, Junior Admin
Don't worry about deleting the other post. I put a duplicate thread notice on it.

I don't know if it is possible to put an IF statement on a SUMPRODUCT formula like that, which means I don't know if it is possible to disregard the garbage. I think you need to control/limit the "garbage" coming in through Data Validation or event procedure VBA, like I posted.

Maybe one of the pros, like Aladin, can devise a formula that does what you want.

#### Brian from Maui

##### MrExcel MVP
winrz said:
I have this formula which looks at the dates in column N and compares it to another, column M. It does a good job at telling me how many entries there are for Jan, how many for Feb, etc..

what I cant figure out is as soon as someone puts a "N/A" into the date column N, this formula chokes.. I cannot figure out how or where to put an IF statement to look for the "N/A" and disregard it.

=SUMPRODUCT((MONTH(\$N\$17:\$N\$25)=MONTH(M17&0))*(YEAR(\$N\$17:\$N\$25)=\$J\$2))

N = column input for month and year
M = column that houses the 12 months to compare against
\$J\$2 = 2004

Is it #N/A or N/A?

#### winrz

##### Board Regular
No, i see what you are asking, its not an error from Excel. It is NA for not applicable. Lets assume its the word 'Purple' for argument sake.

##### MrExcel MVP
winrz said:
No, i see what you are asking, its not an error from Excel. It is NA for not applicable. Lets assume its the word 'Purple' for argument sake.

=SUMPRODUCT(--(TEXT(\$N\$17:\$N\$25,"m/yy")=TEXT(M17&\$J\$2,"m/yy")))

Replies
3
Views
88
Replies
1
Views
101
Replies
27
Views
468
Replies
0
Views
89
Replies
3
Views
210

1,186,173
Messages
5,956,367
Members
438,248
Latest member
Poysenberries

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

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