I am using the formula below to count the number of dates in a certain month
and year from a random list of dates. However my "list" of dates is not a
continuous string of dates and is split by some text and some headings. When
i use this formula it highlights a value error. Is their any way around this
??

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009))

Try
=SUMPRODUCT(--(MONTH(IF(ISNUMBER(A1:A100),A1:A100,0))=2),--(YEAR(IF(ISNUMBER(A1:A100),A1:A100,0))=2009))

This is an Array formula, so press CTRL-ENTER-SHIFT after typing/pasting.

"Dilly" wrote:

I am using the formula below to count the number of dates in a certain month
and year from a random list of dates. However my "list" of dates is not a
continuous string of dates and is split by some text and some headings. When
i use this formula it highlights a value error. Is their any way around this
??

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009))

Sheeloo wrote...
Try

=SUMPRODUCT(--(MONTH(IF(ISNUMBER(A1:A100),A1:A100,0))=2),
--(YEAR(IF(ISNUMBER(A1:A100),A1:A100,0))=2009))

This is an Array formula, so press CTRL-ENTER-SHIFT after typing/pasting.
....

Yes, it is an array formula, so why not use

=SUM(IF(ISNUMBER(A1:A100),(MONTH(A1:A100)=2)*(YEAR (A1:A100)=2009)))

or avoid array formulas and use

=SUMPRODUCT(--(TEXT(A1:A100,"yyyy\-mm;;;")="2009-02"))