How do I sum column C based on a value in Column A?
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? |
How do I sum column C based on a value in Column A?
=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? |
How do I sum column C based on a value in Column A?
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? |
All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com