SUMIF formula works in Excel 2003, does not work in Excel 2007

[Harry]

Folks:
I have an example of a SUMIF formula that works in Excel 2003, but not in
2007.
We did an IF test of one of the cells in the evaluation range with the
criteria, and it came back TRUE (in other words, the IF formula confirmed a
MATCH).

Has anyone else seen this issue in Excel 2007?

thanks,
Harry

 

[T. Valko]

You'll need to provide more detail.

What's the formula look like?

What's the data look like?

Where is the data?

What's the criteria being used?

 

[Sheeloo]

No.

Pl. share the formula and sample data.

 

[Harry]

My apologies... it appears the issue is resolved. regardless, I'll do my
best to explain it, so everyone can gain from the insight.

The entire formula is actually 5 SUMIF formulas added together.
2003: Each one evaluates into the correct answer
2007: Each one evaluates to 0 (incorrect)

(for convenience purposes, I am providing the first of the 5 in the cell)

=SUMIF(H$6:AC$6,AP$2,H1018:AC1018)

H6 through AC6 refer to cells that are supposed to represent dates.
Ex: in L6, we have 04/20 formatted as General.

AP2 is formatted as text, and contains '04/20

Both Excel 2003 and Excel 2007 consider this to be a match, but only in
Excel 2003 does this work.
However, we changed each of the cells in H6:AC6 to actual dates using
DATEVALUE (the dates are all in 2009). L6 now reads DATEVALUE("04/20") and
evaluates to a date (integer 39923, or 04/20/2009) . The SUMIF now works in
both 2003 and 2007.