I have a spreadsheet with 4 worksheets. On sheets 2-4 I gather data from
different sites. I want to subtotal the hours in column C based on the date
in column A. I know about the subtotal menu option but as this spreadsheet
gathers more data throughout the year the subtotal routine will become an
issue. I'm into February now and it's taking about a minute to subtotal each
worksheet.

I'm wondering if there's a forumul that could be created on Sheet 1 to
reference each date range on sheets 2-4.

Date Start Lunch Dur Stop Hrs
01/02/06 8:00 12:00 1:00 17:00 8.00
01/02/06 9:00 13:30 1:00 17:30 7.50
01/02/06 10:30 13:45 1:00 19:30 8.00
01/03/06 8:00 13:00 1:00 18:00 9.00
01/03/06 13:00 16:00 1:00 21:30 7.50
01/03/06 13:30 17:00 1:00 21:30 7.00

I want to total the Hrs. column based on the Date colum and give a total for
each day. Is there a formula that can do this?

=sumif(Sheet2!A:A,date(2006,1,31),Sheet2!F:F) will add up all the hours on
1/31/06 from sheet2. If you already have the date in a different cell, you
can use that instead: =sumif(Sheet2!A:A,a1,Sheet2!F:F). Then just add the
three sumifs together (one each for sheets 2, 3 and 4).

"El Bee" wrote:

I have a spreadsheet with 4 worksheets. On sheets 2-4 I gather data from
different sites. I want to subtotal the hours in column C based on the date
in column A. I know about the subtotal menu option but as this spreadsheet
gathers more data throughout the year the subtotal routine will become an
issue. I'm into February now and it's taking about a minute to subtotal each
worksheet.

I'm wondering if there's a forumul that could be created on Sheet 1 to
reference each date range on sheets 2-4.

Date Start Lunch Dur Stop Hrs
01/02/06 8:00 12:00 1:00 17:00 8.00
01/02/06 9:00 13:30 1:00 17:30 7.50
01/02/06 10:30 13:45 1:00 19:30 8.00
01/03/06 8:00 13:00 1:00 18:00 9.00
01/03/06 13:00 16:00 1:00 21:30 7.50
01/03/06 13:30 17:00 1:00 21:30 7.00

I want to total the Hrs. column based on the Date colum and give a total for
each day. Is there a formula that can do this?

Thank You so much!!! This was driving me crazy! This worked like a champ!

"bpeltzer" wrote:

=sumif(Sheet2!A:A,date(2006,1,31),Sheet2!F:F) will add up all the hours on
1/31/06 from sheet2. If you already have the date in a different cell, you
can use that instead: =sumif(Sheet2!A:A,a1,Sheet2!F:F). Then just add the
three sumifs together (one each for sheets 2, 3 and 4).

"El Bee" wrote:

I have a spreadsheet with 4 worksheets. On sheets 2-4 I gather data from
different sites. I want to subtotal the hours in column C based on the date
in column A. I know about the subtotal menu option but as this spreadsheet
gathers more data throughout the year the subtotal routine will become an
issue. I'm into February now and it's taking about a minute to subtotal each
worksheet.

I'm wondering if there's a forumul that could be created on Sheet 1 to
reference each date range on sheets 2-4.

Date Start Lunch Dur Stop Hrs
01/02/06 8:00 12:00 1:00 17:00 8.00
01/02/06 9:00 13:30 1:00 17:30 7.50
01/02/06 10:30 13:45 1:00 19:30 8.00
01/03/06 8:00 13:00 1:00 18:00 9.00
01/03/06 13:00 16:00 1:00 21:30 7.50
01/03/06 13:30 17:00 1:00 21:30 7.00

I want to total the Hrs. column based on the Date colum and give a total for
each day. Is there a formula that can do this?