countifs based on year ignoring value with #value! error

[fayez_MrExcel]

I have a range of dates where i am counting the number cells where the year is equal to today's year.

=SUMPRODUCT(--(YEAR(A2:A24)=YEAR(TODAY())))

However, it returns #value! because there are many cells where the value is not a date e.g. the cell contains as "---" Or "Not applicable".

How can ignore the #value! in this case

 

[Phuoc]

Try this:

=SUMPRODUCT(--(TEXT(A2:A24,"yyyy")=TEXT(TODAY(),"yyyy")))

 

[fayez_MrExcel]

Try this:

=SUMPRODUCT(--(TEXT(A2:A24,"yyyy")=TEXT(TODAY(),"yyyy")))

Thank you Phuoc. that works perfect