Using COUNTIF or SUMPRODUCT for Year

[PSmith]

I have a column of dates in numeric format of dd/mm/year, and have tried two
different formulas to count the number that fall in a specific year.

When I use the formula
=SUMPRODUCT(--(YEAR(C4:C24)=2001))
it returns the #VALUE! error

I've tried variations of the COUNTIF formula, to no avail either. Any
suggestions? Thanks

 

[Peo Sjoblom]

It's because you have text values in C4:C24 and YEAR will return the value
error

Try


=ISNUMBER(C4)

copy down 20 rows and anywhere you will see FALSE there is a text string

maybe you have blank cells derived from formulas like IF(A1=X,"", A1)
or trailing or leading spaces

--


Regards,


Peo Sjoblom

 

[PSmith]

Thanks for the suggestion. Upon closer examination, I noticed not all were in
the format of dd/mm/yyyy (some were mm/dd/yyyy). thanks once again,

 

[David Biddulph]

What does =YEAR(C4) return (and similarly for the rest of column C)? Are
you sure that you haven't got text, rather than dates, in column C? If you
have, Excel will often translate them to date if they are in the format
expected by your regional settings, but if (for example) your windows
regional settings are looking for mm/dd/yyyy, you'll probably get #VALUE!
responses if you've got dd/mm/yyyy text entries. Similarly if you have a
non-date entry, you'll get #VALUE!