# Rota

1. ## Rota

Hello, I am trying to simplify a works rota. What I want to do is a final calculation of a single person's hours.To do this I want to check a column with the different workers names in it. If for e.g. Monday 1st has "bob" working, then i want to add the figure in the hours worked colum together to give a sum total for the month. The final monthly hours sum will check for every occurence of "bob" and add the corresponding hours worked.

So if "bob" is in a cell, take the number in the adjacent cell (e.g. 8 hours) and add it to every occurrence of "Bob" for the month.
A B
1 Hours Person
2 8 Tom
3 6 Bob
4 6 Ray
5 8 Bob
6 9 Mike
7 8 bob

Worker Bob hours worked = 22
if CELL F50(for e.g.)="bob" then check column B for "bob" and add every corresponding cell from column A to give the answer in CELL F51(for e.g.)
This being a first time post I hope i have explained well enough.

Thanks for any replies.  Register To Reply

2. ## Re: 2 questions, works rota and stock ordering  Register To Reply

3. ## Re: Rota

How is your data laid out? can you upload a sample? or will the whole months data be laid out in Col A with Employees in B?  Register To Reply

4. ## Re: Rota

if everythings in the same row this will do it, assuming your range is A1:A49 for the hours and B1:B49 for the employee name

SUMIF(B2:B49,"Bob",A2:A49)

PS I typed "Bob" into the cell, you could have his name in a cell and refer to the cell instead  Register To Reply

5. ## Re: Rota

The formula works if I type the persons name into the formula
SUMIF(B2:B49,"Bob",A2:A49)

What i have tried to do to make it quicker to input data is create a drop down list of employees names. So what I want to do is select a persons name in CELL A50 for e.g from the drop down list and then use this CELL to workout the hours.Changing the formula to:-
SUMIF(B2:B49,"A50",A2:A49)
so the(B2:B49) range is correct, the (A2:A49)sum_range is correct, but the ( "A50") criteria seems to be the issue.I have tried formatting the CELL as general/custom/text.I'm guessing I just need to change the "A50"??  Register To Reply

6. ## Re: Rota

As long as the listed names in the Drop down are exactly the same as those in your employee list then that formula you posted should work. Just to clarify, you don't need to typ the cell A50 into the formula, just click on it. If you are typing it, don't use any quotes round it  Register To Reply

7. ## Re: Rota

It was the "" causing the problem. Thanks for the answer. It's easy when you know how!  Register To Reply