The values in these cells are dates, not text or numbers.
In Excel, dates are really just NUMBERS formatted to look like dates. To see
the numeric value of a date:
Enter today's date in an empty cell: 9/19/2009
Change that cells format to General. You should see the number 40075. That
number represents the number of days since a base date. In Excel the default
base date is 1/1/1900. The base date of 1/1/1900 has the numeric value of 1.
1/2/1900 has the numeric value of 2. 1/3/1900 has the numeric value of 3.
etc., etc. to today's date, 9/19/2009 has the numeric value of 40075. The
40075th day since 1/1/1900. So, when you enter a date in a cell Excel
automatically recognizes that you've entered a date and formats the cell to
look like a date *but* the true underlying value of that cell is a number.
This number is commonly referred to as the date serial number.
When I use the LEN function on the date cell it returns 1/5.
Since the true underlying value of a date formatted cell is a number then a
LEN of 5 should be expected if the dates you're dealing with are on or after
5/18/1927 ( date serial number 10000). For 1/1/1900 the LEN function would
return 1.
If you get a LEN of 1 then that means there's something in that cell.
To troubleshoot you can count the number of dates in the range. Every cell
that contains a true Excel date will equal 1.
=COUNT(I6:I37)
What result do you get with that formula? Does the result of that formula
agree with the number of dates you have entered in the range? If not, then
some of, maybe all of those cells don't contain true Excel dates.
Do the same thing with your criteria cell H11.
=COUNT(H11)
You should get a result of 1 if H11 is a true Excel date.
Do the same thing with your sum range D6
37
=COUNT(D6
37)
What results do you get from taking these steps?
