SUMIF Criteria

[Champskipper]

I am trying to use SUMIF to add values in one column if there is a date entry
in another column. The formula: =SUMIF(I6:I37,">="&H11,D637) does not work
- always returns zero. The date in H11 is the earliest date in the worksheet.

Can someone suggest what I am doing wrong?

 

[T. Valko]

You probably have either data type mis-matches or unseen whitespace
characters in your data.

See this:

http://contextures.com/xlFunctions02.html#Trouble

Although the page is related to a different function, the trouble it
describes applies to any and every application.

 

[Champskipper]

The values in these cells are dates, not text or numbers.

When I use the LEN function on the date cell it returns 1/5. I'm not sure
what this means.

Is there some other way to test for a non-blank cell in the SUMIF function?

 

[T. Valko]

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 D637

=COUNT(D637)

What results do you get from taking these steps?

 

[Champskipper]

I found the problem - the dates in the range - I6:I37 were not formatted as
dates. I tried to reformat these cells but could not, so I am not sure what
the formatting is.

So I switched the range to another column an voila! The formula worked.

I am not sure what happened, as I did originally format all of these columns
as a date.

Thanks for the help!

 

[T. Valko]

Good deal. Thanks for the feedback!