sumproduct formula only counting dates in column

I have column from a software export that contains dates, yet some of them are in a convoluted format, no matter what i do i can't unify the whole column into date format, so i'm now trying to do a near as count of occurrences in the column, say how many occurences in each month of each year,

so i started with the formula

=SUMPRODUCT(('Export 02-08-2012'!M:M>=A2)*('Export 02-08-2012'!M:M<=B2))

and then tried

=SUMPRODUCT(IFISDATE(('Export 02-08-2012'!M:M>=A1)*('Export 02-08-2012'!M:M<=B1)))

the first bringing back the #VALUE! error, the second the #NAME? error

any suggestions folks?

The formula =SUMPRODUCT(('Export 02-08-2012'!M:M>=A2)*('Export 02-08-2012'!M:M<=B2)) is taking into account that column a and column b contain the start date and end date for each month i'm trying to get a return against

there are also blanks in the column, basically the column is about 82000 rows, and i just need as best count as possible of what dates in the column are count-able, ignoring non dates and blanks

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

Thanks for the input but it returns the same #VALUE! error

---------- Post added at 01:56 PM ---------- Previous post was at 01:54 PM ----------

I've done that and it doesn't convert into date format, not only does it not correct the mdy to dmy format, but the resulting output reading same as it did before can't then be changed into other date layout format, and as such the for,ula still errors

Originally Posted by NBVC

The dates in column M are currently text strings... to fix that, select column M and go to Data|Text to Columns, click Next twice and in 3rd window, select Date in the column data format section, then select MDY or DMY based on your regional setting.. then your formulas should show results... but you should use defined range instead of whole column M:M for efficiency.

I've done that and it doesn't convert into date format, not only does it not correct the mdy to dmy format, but the resulting output reading same as it did before can't then be changed into other date layout format, and as such the formula still errors

You need to put a sum value in.

Example

=SUMPRODUCT(('Export 02-08-2012'!M:M>=A2)*('Export 02-08-2012'!M:M<=B2)*('Export 02-08-2012'!M:M))

Give this a try and let me know.

I've not the greatest with Sumproduct so let me know how it works out

No, in my master s/sheet that hold 82000 records on one tab, and is in total a 42mb file, but the formula should run, the text to columns seems to have unified the date column in the export tab?.....

Originally Posted by NBVC

Can you attach that workbook with that column as is... remove all other impertinent columns

here you go

i should have put a column in with a sequential count so please don't sort or i won't be able to paste it back in

thanks

that worked perfectly, thanks for all your help, much appreciated!

@ jyadayada

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.