sumproduct formula only counting dates in column

**NBVC** · 08-09-2012, 08:07 AM

In Excel 2003, you can't use whole columns with SUMPRODUCT... and if column M contain dates, the first one should work.

Can you post a sample workbook showing what you've got and what you are looking to accomplish?

Also ISDATE is not a valid function.

**jyadayada** · 08-09-2012, 08:32 AM

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

**Max, Singapore** · 08-09-2012, 09:29 AM

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

**jyadayada** · 08-09-2012, 09:50 AM

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

**Max, Singapore** · 08-09-2012, 10:00 AM

> .. Thanks but still returning the #VALUE! error
Formula should work fine, that just means your source data in Export's col M contains at least 1 instance of #VALUE!
Use AutoFilter on col M to locate all of these cells with #VALUE!, clear them with the DELETE key