vlookup???

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
November Forced Disconnects.xls
ABCDEFGH
1Hours11/1/0211/4/0211/5/0211/6/0211/7/0211/8/0211/11/02
20:00
30:30
41:00
51:30
TRAC


Does this help?
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGHI
1Hour11/1/0211/2/0211/3/0211/4/020:3067
20:00531112:00
30:3077106
41:003389
51:306242
62:004655
72:309830
Sheet3
</SPAN>

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
PERFECT!! Thank you all for your time!!!

Dave M.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top