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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
61,028
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
61,028
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
61,028
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,209
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,209
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")))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,819
Messages
5,772,461
Members
425,760
Latest member
paphon

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
Top