?
Solved

Excel 2007 Function to calcualte TotalHours based upon multiple criteria

Posted on 2011-09-30
4
Medium Priority
?
222 Views
Last Modified: 2012-05-12
Hello to all Excel Gurus,

I would like to create a function to sum the total number of hours (Break/Lunch) based upon selections made in drop downs.
Please note, anyone of the shift hours can be chosen and it must correspond to the correct break and lunch calculation.

For example:
1stShift   2ndShift
8              10

For 8 hrs you get .5break, .5 Lunch = 1hrs
For 10hrs you get .75break, .5 Lunch = 1.25hrs
Total time = 2.25Hrs
This is just for one day, I need to be able to do this *5days and for whatever combination of shift hours chosen.

I created Named ranges for my drop downs (On Schedule Tab) called
Day_Night_ShiftHours
Shift_Breaks
Shift_Lunch

The Data is stored on Hours tab

Please help if possible.


Function.png
0
Comment
Question by:BajanPaul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36895956
For future, it would be EXTREMELY useful to put a dummy spreadsheet as it takes us time to re-create what you have in pictures to create a solution.  I took that time to create the base data, ignoring where your named ranges are, believing that you could catch up that step.

You could create an additional column on the Hours tab to add the hours together, in total.  THEN, you could create a series of vlookups for each of the shifts to get what you want:

E.g.,  =Vlookup(shift1hours,the table with extra column, 3,0) + vlookup(shift2hours,.... etc., etc., etc.,

However, you could also create helper dataset to the right of your summary table (that table you made yellow) that had a vlookup to shift1, then copy/paste to the number of shifts you'll ultimately have.  I did that.  I created "helper" data in range S1:AB3, with vlookups like (for S3):

=VLOOKUP(B3,Hours!$A$2:$D$8,4,0)  and dragged that for all the shift/date combinations clear to AB3.

Then, its easy to sum that up for your total.

See attached,

Dave
shiftBreaks-r1.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36896069
You could get the same results with a single formula with no additional helper cells, i.e.

=SUMPRODUCT(SUMIF(Hours!A$2:A$8,B3:K3,Hours!B$2:B$8)+SUMIF(Hours!A$2:A$8,B3:K3,Hours!C$2:C$8))

or assuming you add a total column to the hours table in column D as per Dave's example that could be simplified to this

=SUMPRODUCT(SUMIF(Hours!A$2:A$8,B3:K3,Hours!D$2:D$8))

regards, barry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36897409
That's some good wizardry, barry!
0
 

Author Closing Comment

by:BajanPaul
ID: 36912205
Thanks for the help.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question