Forum Discussion

Deleted's avatar
Deleted
Jul 11, 2018

Adding a value that is anywhere on a worksheet based on a word in the cell above the value.

Hi Everyone,   I am creating a scheduling tool. With this tool I am adding hours based on the position the worker is doing. I have drop down menus set and when the down drop position is populated I...
excel
Formulas and Functions
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jul 11, 2018

Hey Scott-

 

Can you please provide a small non-sensitive example of the way your data is represented so the community can better address your scenario.  Sometimes it's a little easier to create a solution with a little visual aid.

Deleted's avatar
Deleted
Jul 11, 2018

  • Deleted's avatar
    Deleted
    Jul 11, 2018

    As you can see, Bob is doing 3 different jobs over the next 3 days.

    I would like to automatically add Bob's and others different positions hours to the top row.

    I hope that helps.

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jul 11, 2018

      The way you have your data structured you will probably want to use SumProduct().  Please see attached .xlsx file for reference.

       

      For Example: 

       

      =SUMPRODUCT(($B$10:$G$18="E&M")*($B$11:$G$19>0),$B$11:$G$19)*24

       

      • Deleted's avatar
        Deleted
        Jul 11, 2018

        Thanks for trying.

        I'm not sure that I'm making myself clear enough.

         

        My worksheet is from B11:O219.

        There are 7 days and 50 employees.

        I input their Start and End times for their shift and excel populates the hours.

        expample:

        Bob is a Donor Greeter on Monday B11=7:00 hours

        Bob is in E&M on Tuesday D11=7:30 hours

        Bob is in Wares on Wednesday F11=7:30 hours

         

        I would like all times/(Value) under "Donor Greeter" within the B11:O219 area to be added in B4

        I would like all times/(Value)  under "E&M" within the B11:O219 area to be added in D4

        I would like all times/(Value) under "Wares" within the B11:O219 area to be added in C4 etc.......

         

        Thanks again for the help

         

        Scott