Unable to count the occurrence of years in a list that contains dates and text

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

Hi

Cumbersome, but it seems to work.

Please Login or Register  to view this content.

Note that this is for non mac excel. Don't know the syntax for Mac version.

rylo

Thanks Rylo. Regrettably it didn't work.

The outcome is 0 (zero). Also when I change YEAR 2012 to e.g. 2011

Gijs

Thanks Haseeb. That works. I'll add separate formulas to calculate the instances of NO ACCOUNT or FUTURE ACCOUNT in the same Q1:Q640.

Thanks again, Gijs