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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,836
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 4, 2002
Messages
106
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
Joined
Aug 1, 2002
Messages
54,836
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 4, 2002
Messages
106

ADVERTISEMENT

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
Joined
Aug 1, 2002
Messages
54,836
Office Version
  1. 365
Platform
  1. Windows
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Sep 4, 2002
Messages
106
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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")))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,028
Members
410,583
Latest member
gazz57
Top