Q1:Q640 contains:
  • Dates (entered as 03/27/2012 and displayed as 27-Mar-12)
  • Text (entered as NO ACCOUNT or FUTURE ACCOUNT)

In Q641:Q700 (in combination with P641:P700) I want to list some statistics of the entries in Q1:Q640:
  • P641:P700 lists the years entered as 1997, 1998, 1999 etc. and NO ACCOUNT and FUTURE ACCOUNT
  • In Q641:Q700 I want to display the number of times a text or a date was entered in Q1:Q640 that matches the YEAR or the text in P641:P700
  • Since I expect the text entries in Q1:Q640 to change (and subsequently the list of requirements and cells P641:P700 to be expanded to e.g. P701 etc.), one formula that combines both the dates and text field would be desirable.

In an attempt to first count the dates, and even without using P698:P700 as a reference, I used the formula =SUMPRODUCT(--(YEAR(Q1:Q640)=2012)) unsuccessful since it gave as result #VALUE!

Can anyone help, please. Thank you in advance,

Gijs