Hello,
I have a table created to list job tasks performed. Please see attached for an example of the table. Each row is a single task, while the columns represent different information related to the task, which includes the date & the hours worked for that entire date (not hrs worked on the task). The table would be ongoing, each day adding new data to it, and serve as a master list for tasks performed.
Issue:
On another sheet/workbook, I need to calculate the hours worked for a date range. This spreadsheet would be set up with a From-date cell & a To-date cell, and based off those dates entered, I need to calculate from the task list spreadsheet the total hours worked in that date range. For example, using the attachment, how can I calculate the total hours worked from 11/8/11 to 11/10/11?
Not sure if a formula would work, or if i need to re-think the design of the table? Any help/input/suggestions would be appreciated! Thanks!
Last edited by jellyjen17; 12-13-2011 at 06:54 PM.
Possibly with SUMIFS.
e.g.
=SUMIFS(Daily!$R$4:$R$13,Daily!$C$4:$C$13,"<="&A1,Daily!$C$4:$C$13,"<="&B1)
where A1 and B1 contain the start/end dates, respectively.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hello,
I've actually tried a few variations of this formula, but to no success. I apologize, as I probably should have mentioned that with my intial posting.
Regarding this particular formula, it appears to say add hours (hours column, r) that are less than or equal to the to-date (cell b1) AND less than or equal to the from-date (cell a1), correct? With a from-date of 11/8 & to-date of 11/10, I'm understanding this formula to calculate basically just the hours associated with 11/8, which with the attached table, there are 3 lines with an 11/8 date with hours on each line equal to 4.25. This totals 12.75, which is what i get using this formula. In addition, the total hours worked for 11/8 is 4.25, and it seems with the sumifs formula, it wants to add 4.25 hours 3 times since i have 3 tasks with that date.
Sorry the formula should've been:
=SUMIFS(Daily!$R$4:$R$13,Daily!$C$4:$C$13,">="&A1,Daily!$C$4:$C$13,"<="&B1)
to give 24.75 hours
but are you looking to sum only once per date?
If so:
confirmed with CTRL+SHIFT+ENTER not just ENTER=SUM(IF(FREQUENCY(IF(Daily!$C$4:$C$13>=A1,IF(Daily!$C$4:$C$13<=B1,IF(Daily!$C$4:$C$13<>"",MATCH(Daily!$C$4:$C$13,Daily!$C$4:$C$13,0)))),ROW(Daily!$C$4:$C$13)-ROW(Daily!$C$4)+1),Daily!$R$4:$R$13))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This worked! Thank you much for your help! Yes, I wanted to sum only once per date, & the formula you supplied (w/ CTRL+SHIFT+ENTER) is just what I needed. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks