+ Reply to Thread
Results 1 to 7 of 7

Rota

  1. #1
    Registered User
    Join Date
    03-13-2011
    Location
    Midlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    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.
    Last edited by -Doubleoseven-; 03-14-2011 at 05:36 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: 2 questions, works rota and stock ordering

    Can you ask unrelated questions in a separate thread
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    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?
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  4. #4
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    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

  5. #5
    Registered User
    Join Date
    03-13-2011
    Location
    Midlands
    MS-Off Ver
    Excel 2003
    Posts
    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"??

    Thanks for your first reply.

  6. #6
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    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

  7. #7
    Registered User
    Join Date
    03-13-2011
    Location
    Midlands
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Rota

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1