sumproduct formula only counting dates in column

Originally Posted by Max, Singapore

Think you could try something like this in E1, copied down:
=SUMPRODUCT(--(TEXT(Export!M$2:M$100,"mmm yyyy")=TEXT(B1,"mmm yyyy")))
where in B1 down are real dates, eg 1st of month dates such as 01 Jan 2001, 01 Feb 2001, etc

TEXT is used here to extract the "month-year" from the data in col M in Export and from col B for the comparison
Sumproduct then adds it up all the instances where the "month-year" comparison matches

Thanks but still returning the #VALUE! error

---------- Post added at 02:50 PM ---------- Previous post was at 02:49 PM ----------

Originally Posted by NBVC

Here is your sheet returned with Text to Columns applied to column M.

Notice that the dates in the first sheet are all in 2001.. whereas the dates in column M are 2011 and 2012...so you will get 0 results if you don't have matches because of the year...

On re-running the text to columns has worked, alas the formula is still returning a #VALUE! error