Excel 2007 : Count based on year in a row of dates

I have an Excel file that lists a date that corresponds to each time a photo was used. I need to count the usage based on a specified year, but haven't found the right function to do this. Here's a rough example:

Photo name | Usage | Date(C | Date(D) | Date(E)

MyDog.jpg | #here | 1/1/2010 | 3/7/2010 | 1/21/2011

The usage column needs a function that counts the number of uses in 2010 based on columns C, D and E (there are actually columns out to Z). So, in this example, the number in the usage column should be 2.

I've tried COUNTIF and SUMPRODUCT, but so far have been unsuccessful. Thanks in advance for any help you can provide.

Kevin

The above is backwards compatible with versions prior to XL2007- the below is not:

Please Login or Register  to view this content.

If you're not using XL2007 it would be best to update your profile accordingly (and/or state version requirements in question)

Originally Posted by Dadoo

DATE(2010, 1, 1) is evaluating to a serial number rather than a date.

To be clear - in XL Dates are just Serial Numbers (Time being Decimal) - the Format applied to the Number determines the appearance (ie number or date)

I'd suggest posting a sample file to avoid further confusion.

I've attached a sample file. Thanks again!

Kevin

P.S. Just did a test on the Excel file. Looks like column D is a number, but columns E thru G are not. They came from the same source file, but perhaps something I did to add delimiters to the file changed the format of every field after D or something. So perhaps the crux of the problem is to get all of the date rows into the same format?

The dates in col D are actual dates
The "dates" in cols E to G are not - they are text and they have leading spaces.