vlookup???

[Davers]

Hello again, I'm full of questions today! I have a sheet that lists hours in column A in half hour increments starting at 0:00. So, A2 is 0:00, A3 is 0:30...etc. Column B through V are dates. Column B1 is 11/01/02, B3 is 11/02/02...etc. The number of calls received in that half hour time go from cell B2:V49. What I'd like to do...is have a cell I can type a start time in, and a cell I can type an end time in, and have it return the # of calls received between those two times for the entire month. For instance in cell AA2 I would type: 08:30, and in cell AA3, I would type 17:30. It should return the sum of calls received for the whole month between those 2 times and put that number is say...cell AB2.

Any ideas???

Thanks,

Dave M.

 

[Juan Pablo González]

I'm a bit confused, in Row 1 appear the dates ? you're saying that they appear in B1, B3, etc., and then you say that the number of calls appear in B2:V49 (Which would erase the content of B3)...

Do you use one sheet per month ? or do you list many months in that sheet ?

 

[Davers]

Heh...oops...the dates appear in B1,C1,D1....through V1...sorry. I month per sheet. I quoted the times correctly. They are all in Column A2:A49.

Does that help a little...

Thanks,

Dave M.

 

[Davers]

November Forced Disconnects.xls
	A	B	C	D	E	F	G	H
1	Hours	11/1/02	11/4/02	11/5/02	11/6/02	11/7/02	11/8/02	11/11/02
2	0:00
3	0:30
4	1:00
5	1:30
TRAC

Does this help?

 

[maxflia10]

Not sure if this is what you want but...

=SUMPRODUCT((A1:A3>=("08:30:00"+0))*(B1:B3<=("17:30:00"+0)),C1:C3)

Where column A houses a start time, column B the end time and column C the number of calls. I'm sure someone will come up with a shorter and/or better formula.

 

[Yogi Anand]

Hi Davers:

In addition to the valuable contribution from Maxflia10, ...
I have taken a small set of data, determined the range of applicable values using the MATCH function and summed them up ... see the worksheet simulation

Book1
	A	B	C	D	E	F	G	H	I
1	Hour	11/1/02	11/2/02	11/3/02	11/4/02			0:30	67
2	0:00	5	3	1	11			2:00
3	0:30	7	7	10	6
4	1:00	3	3	8	9
5	1:30	6	2	4	2
6	2:00	4	6	5	5
7	2:30	9	8	3	0
Sheet3

</SPAN>

Regards!
Yogi Anand
This message was edited by Yogi Anand on 2002-11-06 13:22

 

[Yogi Anand]

Hi Davers and Maxflia10:

The ranges in Maxflia10's SUMPRODUCT formula will have to be adjusted. If i may use the sample table in my simulation, then the SUMPRODUCT formula should read ...

=SUMPRODUCT((A2:A7>=H1)*(A2:A7<H2)*(B2:E7))
and should result in 67

Regards!
Yogi Anand
This message was edited by Yogi Anand on 2002-11-06 13:31

 

[Davers]

PERFECT!! Thank you all for your time!!!

Dave M.