Countif problem with N/A

winrz

Board Regular
Joined
Sep 4, 2002
Messages
106
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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")))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top