Countif problem with N/A

Status
Not open for further replies.

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,765
Members
410,704
Latest member
Cobber2008
Top