Excel not finding values in a named range

[Lifferds]

In my spreadsheet I have multiple worksheets. On one worksheet are a listing of trades in a futures contract including Date, Time, Price, etc. The times are imported from a .csv with time data in 8:00 format, displayed in the formula bar as 8:00:00 AM and formatted to 8:00. There are about 625 of them, with same and different times by date, depending on when the trade triggered.

On another spreadsheet I have rows for each minute of the open and close time frames (9:30, 9:31, 9:32, ...). In each cell under that row is the formula: {Sum(If(TTime = "cell above", Ticks,0))} where TTime is the Named Range for the trade times and Ticks is the Profit/Loss. I can look in the Data woksheet and see trades for 9:45, for instance, but the formula in the cell under the 9:45 time on worksheet 2 returns 0.

If I look in Name Manager at the values, there are no values for 9:45 listed even though they are there. The Range defined for TTime is correct (D6:D628) and encompasses all trade times.

Can someone help?

[rcm]

Could you send a non-sensitive sample? I suspect it has to do with the time format

[FDibbins]

1st, check to see if some of the times are actually text that just looks like time/value. you can do this using =isnumber(cell_ref) FALSE indicates text

2nd try using =sumifS() this wont change anything if the data is text, but is more flexible than the array formula you are using

If all else fails, refer to post #2
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Lifferds]

SUMIFS works fine with the following caveat:

I entered Times 8:00, 8:01, 8:02 in the first three cells in row one, 9:00, 9:01, 9:02 in the first three cells of row 3 and followed that pattern to the 14:00 row.
I then highlighted these three cells in those rows only. Then I drag-copied to the right to auto fill the rest of each hour's row.
I entered the formula SUMIFS(TTime,Exit3Ticks,"=" & 'cell directly above') in the cell below the :00 times and drag copied across to cover all times.
Some formulae calculated fine, some don't, However, if I type in the actual 8:40 in the failing 8:40 cell, the formula recalcs fine. If I re-drag-copy the time, the formula fails.

IsNumber returns True on all time cells and Named Range time cells.

The Spreadsheet contains no sensitive data but is over 4 mb.

[FDibbins]

can you cut it down a bit? maybe remove any data/sheets that does not pertain to your question? only use just enough rows/columns of data to show what you need?

[Lifferds]

Here is a trimmed down version with non-pertinent columns hidden. Sheet 2 contains the formulae in question.

Thanks for your help.

[Lifferds]

I got to thinking about that Time Format issue so looked at the times as a number. Entering 8:40 into cell AO2 on sheet2 resulted in 0.361111111111111, whereas drag-copying resulted in 0.361111111111112. No wonder it fails, they're not equal.

The question now is how do I get the proper numbers entered w/o typing in all 420 values, one for each minute? And why is Excel messing with me?