Why do I need hit the F2 Edit key to get formula to calculate?

[TSum]

Excel 2007. Using a sumif formulas. The formulas work until I get to a certain time for the "criteria" then all remaining times/criteria I need to be on the "criteria" cell then hit F2 and enter to get the formulas to calculate. Auto calc is activated. The cells are numeric in a HH:MM PM format. Times before 3:30 PM work, after do not without F2-enter. Range times are in Ascending order.

 

[Domenic]

Try coercing them into time values...

1) Select an empty cell.

2) File > Clipboard > Copy

3) Select the cells or column containing the time.

4) File > Clipboard > PasteSpecial > Add > Ok

 

[TSum]

I tried your recommendation and it did not work. I alse tried a formula for my time/criteria column and the formula worked but it did not update my sumif cell until I did F2>enter again.

 

[Domenic]

I forgot to mention that the cells containing the time should be formatted as 'Time' before coercing them into time values. Does this help?

 

[TSum]

Thanks for the help. The criteria cell and the range cell are in the same Time format and still not getting it to auto calc for all times. Thinking of converting the criteria and reference columns to text to see if that will work. Possible a different formula is out there that I'm not familiar with?

 

[Domenic]

Can you post a sample of the time values you're dealing with, along with your SUMIF formula?

 

[TSum]

I seem to have found a "solution" although I do not know why it works this way. I converted by "criteria" column to text. Same Time format as before and left the "range" column as value/Time format. Now all of sumif formulas are working. No idea why converting to text is working while value/Time was not. But now need to hit my deadline. Thanks for making me think through other options.