Annual Timesheet: Two date ranges lookup in a single sum column
 

hello,

Below is the annual timesheet requirement, and I need it preferrably in
Excel formula (if not VBA, will also do)

Sheet 1:
Date Day Hours Remarks
1/1/06 Sun 0
1/2/06 Mon 0 New Years Day Holiday
1/3/06 Tue 8
1/4/06 Wed 8
1/5/06 Thu 8
1/6/06 Fri 8
1/7/06 Sat 0
1/8/06 Sun 0
1/9/06 Mon 8
....
....and so on

Sheet 2:
Begin End Total hours
1/2/06 1/15/06 72
1/16/06 1/29/06 80
....
....and so on

What I am looking here is, In sheet 2, Total hours should be calculated
based on looking up Begin and End dates from sheet 2 in sheet 1 and
then computing sum for that range. For example, the formula should
lookup the range of dates 1/2/06 and 1/15/06 in sheet 1 and compute the
sum as 72.

Appreciate your quick response.

-King

Annual Timesheet: Two date ranges lookup in a single sum column
 

=sumif(Sheet1!A:A,"="&A2,Sheet1!C:C) - Sumif(Sheet1!A:A,""&B2,Sheet1!C:C)
in C2
then drag fill down column C of Sheet2

--
Regards,
Tom Ogilvy


"king" wrote in message
oups.com...
hello,

Below is the annual timesheet requirement, and I need it preferrably in
Excel formula (if not VBA, will also do)

Sheet 1:
Date Day Hours Remarks
1/1/06 Sun 0
1/2/06 Mon 0 New Years Day Holiday
1/3/06 Tue 8
1/4/06 Wed 8
1/5/06 Thu 8
1/6/06 Fri 8
1/7/06 Sat 0
1/8/06 Sun 0
1/9/06 Mon 8
...
...and so on

Sheet 2:
Begin End Total hours
1/2/06 1/15/06 72
1/16/06 1/29/06 80
...
...and so on

What I am looking here is, In sheet 2, Total hours should be calculated
based on looking up Begin and End dates from sheet 2 in sheet 1 and
then computing sum for that range. For example, the formula should
lookup the range of dates 1/2/06 and 1/15/06 in sheet 1 and compute the
sum as 72.

Appreciate your quick response.

-King

Annual Timesheet: Two date ranges lookup in a single sum column
 

Tom,

Thanks a lot !! It worked just fine....

Regards,
King

Tom Ogilvy wrote:
=sumif(Sheet1!A:A,"="&A2,Sheet1!C:C) - Sumif(Sheet1!A:A,""&B2,Sheet1!C:C)
in C2
then drag fill down column C of Sheet2

--
Regards,
Tom Ogilvy