OK, I got it. You'll be getting my email reply any minute.

It was a formatting problem. The times were really TEXT strings that looked
like times.

--
Biff
Microsoft Excel MVP


"Whitney" wrote in message
...
I sent it to xlhelp

"T. Valko" wrote:

Can you send a copy of the file to me so I can see what's going on? If
so,
I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"Whitney" wrote in message
...
I copied the exact formula from the spreadsheet, hit ctrl+shift+enter
and
got
the bracketts, but it's still coming back blank for all time frames.

I even checked the fromating for columns B & C, B is h:mm:ss, c is
number.

Is there something else that I'm missing?

"T. Valko" wrote:

Here's a sample file using your sample data with the results you said
you
should get.

Whitney.xls 15kb

http://cjoint.com/?lheDjBPAZc

--
Biff
Microsoft Excel MVP


"Whitney" wrote in message
...
I copied the formula and hit ctrl+shift+enter and got the bracketts,
but
it's
coming back blank.

"T. Valko" wrote:

the times are always in chronological order.
Within the same minute.

Try this array formula**:

F2 = 6:00:00
F3 = 6:15:00
F4 = 6:30:00
etc

=IF(ISNA(MATCH(1,(B$2:B$39=F2)*(B$2:B$39<=F2+59/86400),0)),"",INDEX(C$2:C$39,MATCH(1,(B$2:B$39=F2 )*(B$2:B$39<=F2+59/86400),0)))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Whitney" wrote in message
...
Within the same minute.

"T. Valko" wrote:

You'll have to explain in greater detail what you mean by
"closest".

6:00:00 - there is no data, returns a blank
6:15:00 - finds 6:15:20, returns 14
6:30:00 - finds 6:30:00, returns 15
6:45:00 - finds 6:45:04, returns 13

Why do you say for 6:00:00 there is no data? There is an entry
for
6:12:56
which would be the "closest" to 6:00:00.

--
Biff
Microsoft Excel MVP


"Whitney" wrote in message
...
Thanks for your quick reply. Actually, I'm not looking to sum
the
numbers.
I
just need the number that is closest to the interval.
For example
6:00:00 - there is no data, returns a blank
6:15:00 - finds 6:15:20, returns 14
6:30:00 - finds 6:30:00, returns 15
6:45:00 - finds 6:45:04, returns 13

"OssieMac" wrote:

Hi Whitney,

Following formulas should work on your sample data. You need
to
make
one
criteria greater than () and the next one less than or equal
to
(<=)
otherwise a time like 6:15:00 would get included in the
6:00:00
to
6:15:00
and also 6:15:00 to 6:30:00. Based on this, you might have to
adjust
the
formulas to return the required values. You might want to
make
the
first
criteria equal to or greater (=) and the second one less
than
(<)

Examples assuming:
Dates are in column A
Times in column B
Agents in column C

6:00 to 6:15
=SUMIFS(C2:C39,B2:B39,"6:00:00",B2:B39,"<=6:15:00 ")

6:15 to 6:30
=SUMIFS(C2:C39,B2:B39,"6:15:00",B2:B39,"<=6:30:00 ")

6:30 to 6:45
=SUMIFS(C2:C39,B2:B39,"6:30:00",B2:B39,"<=6:45:00 ")

6:45 to 7:00
=SUMIFS(C2:C39,B2:B39,"6:45:00",B2:B39,"<=7:00:00 ")

7:00 to 7:15
=SUMIFS(C2:C39,B2:B39,"7:00:00",B2:B39,"<=7:15:00 ")


Feel free to get back to me if this does not answer your
question
as
you
would like.

Regards,

Ossiemac



"Whitney" wrote:

Can anyone help with a lookup formula that will look at the
cell
closes
to
6:00, 6:15, 6:30, 6:45, 7:00, etc. and tell me what the
number
of
agents
we're (cell to the right) in each interval? There is no
pattern,
so
the
lookup value will not always be in the same cell location
each
day.


Date Time Agents Available
11/2/2007 06:12:56 15
11/2/2007 06:14:56 15
11/2/2007 06:15:20 14
11/2/2007 06:15:28 15
11/2/2007 06:15:52 14
11/2/2007 06:16:40 13
11/2/2007 06:22:16 14
11/2/2007 06:23:28 15
11/2/2007 06:23:52 16
11/2/2007 06:24:16 15
11/2/2007 06:24:56 14
11/2/2007 06:25:12 13
11/2/2007 06:25:20 13
11/2/2007 06:25:28 12
11/2/2007 06:28:40 15
11/2/2007 06:29:20 14
11/2/2007 06:30:00 15
11/2/2007 06:30:08 14
11/2/2007 06:30:56 15
11/2/2007 06:31:04 14
11/2/2007 06:32:48 13
11/2/2007 06:44:48 13
11/2/2007 06:44:56 14
11/2/2007 06:45:04 13
11/2/2007 06:45:12 13
11/2/2007 06:45:20 13
11/2/2007 06:45:28 13
11/2/2007 06:45:36 12
11/2/2007 06:45:44 12
11/2/2007 06:45:52 12
11/2/2007 06:59:20 20
11/2/2007 06:59:28 20
11/2/2007 07:00:00 21
11/2/2007 07:00:08 22
11/2/2007 07:00:16 23
11/2/2007 07:00:40 22
11/2/2007 07:01:12 23
11/2/2007 07:02:24 22