Adding Multiple Lines when "IF" is True

[pugsly8422]

I am having a few problems trying to come up with a function that can satisfy this problem. Sometimes one employee may appear under more than one group (bartender, barback, waitstaff, etc.). I need to insert a formula that will check the other groups to see if this employee worked any hours in another group that week (I would assume a VLOOKUP would work), and then distribute the overtime hours accordingly.

If you view the attached worksheet, you can see I've entered a name and some hours. The formulas in cells M2 and N2 are my attempt to fix this problem. Anyway, the way the overtime hours are distributed is based on the group they worked under after they reached 40 hours. So looking at the example on the worksheet, Bob should have all of his overtime (13 hours) appear under O/T in row 2 because the 9 hours he worked on the waitstaff was before he was "officially" earning the overtime.

So I am needing the formula to check all of the groups other than it's own for a duplicate, add the total hours, and distribute the overtime accordingly.

Thanks in advance for any assistance, if there are any questions I can answer to help you out, feel free to ask.

Thanks again.

[NBVC]

Perhaps something like this?

In M2: =IF(ISNUMBER(MATCH(A2,A$17:A$53,0)),A2,"")

In N2: =MAX(0,SUMIF(A:A,M2,L:L)+SUMIF(A:A,M2,K:K)-40)

[martindwilson]

i'd just insert a new column m which has totals sum(d2:j2)
for each person
then use in column N against each name in column A
=SUMIF($A$1:$A$100,"bob",$M$1:$M$100)-40 gives 13 for bob

[pugsly8422]

I appreciate the help from both of you.

Both of your methods seem to get me through the first step, which is figuring out how much overtime the person, in this case Bob, has worked. I am trying to insert the fuctions into L2 and L22, but am getting a circular reference, is there any way to avoid this? If possible, I'd like to avoid using columns M and N all together, but I can always hide those.

If Bob happens to work 2 hours on Sunday on the waitstaff, will the formula show those 2 hours of overtime in L22?

I hope I'm not making this too confusing, I really appreciate all of your assistance.

[NBVC]

So do you want the actual number 13 to appear? If so, where?

[pugsly8422]

So do you want the actual number 13 to appear? If so, where?

Yes, I would like 13 to appear in L2 and 0 to appear in L22.

I added John to the list as well to try and make it easier to understand how the overtime works. Wherever the person is working when anything above 40 hours is earned is where the overtime should go. So in John's case he has 40 hours Mon-Fri as a bartender, so no overtime there. But he worked 10 hours (5 and 5) on the waitstaff after he had reached 40 total hours for the week, so he will be receiving overtime for those 10 hours he worked on the waitstaff, but none for the 40 hours as a bartender. If he had worked 9 hours on Monday then 1 of his hours as a bartender on Friday would have been overtime, as well as all of his hours on the waitstaff.

Thanks again for helping me out with this, I'm sure you can tell it has been driving me insane!!

[NBVC]

I didn't see any data for John as you mentioned.

Also, please resubmit the workbook showing some entries and expected results with explanations on how the results were obtained.

[pugsly8422]

Sorry, I failed to attach the new workbook. I've also added Todd to the list.

Bob - he worked on waitstaff and bartending, once the total hours worked (bartending+waitstaff) reaches 40, everything after that will be overtime and need to go in the L column. Bob had 12 hours Monday, 14 Tuesday, 9 Wednesday, 9 Thursday and 9 Friday for a total of 53 hours. After he reaches 40 hours on Thursday, everything needs to go in the L column, whether he's bartending, a barback, or on the waitstaff.

John - he reached 40 hours at the end of the day Friday, so any hours he works Saturday or Sunday will be overtime. That means the 5 hours he worked on the waitstaff on Saturday and Sunday will be overtime, 10 hours total.

Todd - I put his explanation on the workbook.

Somehow the fuction that goes in L2 will have to know that Bob also worked hours on the waitstaff, and the function in L22 will have to know that he worked hours bartending.

[pugsly8422]

bob #1 would be his hours bartending, bob #2 would be his hours barbacking, bob #3 would be his hours on the waitstaff, bob #4 would be his hours hosting and bob #5 would be his hours training. I bolded every other part of the function to hopefully make it easier to read.

I was thinking L2 would be something like:

if(bobs name appears more than once,if(bob #1 monday hours+bob #2 monday hours+bob #3 monday hours+bob #4 monday hours+bob #5 monday hours>40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday hours-40,if(bob #1 monday thru tuesday hours+bob #2 monday thru tuesday hours+bob #3 monday thru tuesday hours+bob #4 monday thru tuesday hours+bob #5 monday thru tuesday hours>40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday hours-40,if(bob #1 monday thru wednesday hours+bob #2 monday thru wednesday hours+bob #3 monday thru wednesday hours+bob #4 monday thru wednesday hours+bob #5 monday thru wednesday hours>40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday hours-40..........,if(bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday hours>40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday hours-40)))))))

I'm sure that function is impossible to fit in a single cell, so I'm hoping there is an easier way to do it.

[NBVC]

For now I have used this formula in L2, copied down... which gives the results you have in the Bartender area...

=IF(A2="","",MAX(0,SUMPRODUCT(($A$2:$A$53=A2)*ISNUMBER($D$2:$H$53),($D$2:$H$53))-40))

but I am unsure how you get the numbers in column L of the Waitstaff area?

[pugsly8422]

Thanks again for your help. The numbers in column L of the waitstaff area are determined the say as the ones in the bartending area. I got the 10 for John by getting his 40 hours from working monday thru friday bartending, and then him working 5 hours saturday and 5 hours sunday on the waitstaff. If you add up all of Johns hours for the week, he worked 50 total, so he has to have 10 hours of overtime. Those 10 hours will be the last 10 he worked, and the last 10 he worked were on the waitstaff, so that is where that overtime will go.

Great job with the formula, that seems to work perfectly!

[NBVC]

I'm gone for the day.. I can look at it again tomorrow...

[pugsly8422]

Thank you very much.

[NBVC]

Sorry for not coming back yesterday as promised I was not well and laid in bed all day....

Anyways, give this formula a try in L2 and copy it down the whole column.... at the points where the headings of the subgroups are you can overwrite the formula with "O/T" subheading...

Please Login or Register  to view this content.

[pugsly8422]

Sorry for not coming back yesterday as promised I was not well and laid in bed all day....

Anyways, give this formula a try in L2 and copy it down the whole column.... at the points where the headings of the subgroups are you can overwrite the formula with "O/T" subheading...

Please Login or Register  to view this content.

We all get sick, I totally understand. That formula did exactly what I was hoping for. I want to thank you for all of your assistance. Is there a link you can give me where I can make a donation/contribution toward the site?

Thanks again for your help, that formula was amazing!

[NBVC]

I'm not sure the site has facilities for accepting contributions... I think it is ad-sponsored. We don't get paid here... we are all volunteers having fun with Excel.

Thanks for the feedback. Glad the formula worked for you.

[pugsly8422]

Actually, there still seems to be a problem with the formula. Originally it wasn't including Saturday or Sunday in the calculation, but I was able to adjust the formula for that. Now what it is doing is putting all of the overtime in one location.

I think this will be a simple thing to fix, or at least I hope it will.

L2 should say 9 because 9 hours of overtime were earned bartending, and the other 3 hours should be under L24 for barbacking. It is calculating the correct total amount of overtime, just isn't dividing it out correctly. Sorry to keep bothering you with this.

I've attached an updated copy of the spreadsheet and included some notes.

Thanks again.

[NBVC]

This makes it much more complicated....

See attached and hopefully it works for you...

I added some helper columns to determine when the employee passes the 40 hour mark... These columns can be hidden if you wish...

so the formula for the first helper column would be

Please Login or Register  to view this content.

This is copied across 7 columns and down the list....

Then in L2:

Please Login or Register  to view this content.

which is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then you can copy it down.

[pugsly8422]

It looks like that worked and it is officially completed. Thanks again NBVC for all of the time and effort you put into this, it is greatly appreciated!