+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Drop down lists and autosum

  1. #1
    Registered User
    Join Date
    04-30-2012
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2013
    Posts
    20

    Drop down lists and autosum

    So im not sure how to explain in the technical aspect so im just going to show you what im trying to accomplish...

    I have a manufacturing business and I created a spread sheet to track loss and yield on specific products throughout the week... i have 5 different presses that can make any one of the product, so I have all presses listed vertically and each tracking Target, Actual, Loss, Yeild. Horizantally I am tracking production for each day of the work week. For each day, i created a drop down menu for each press which allow me to select the product, then the cell below i type in what my target to produce is, next cell down is what i actually produced, then the following cells formulate loss and yield.

    now heres my question... I want to be able to create a grand total tracker that also has a drop down menu that calculates loss and yield for specific products. for example and ease of understanding we will just pretend i run one press for the week.

    monday tues Wed thur Fri Total
    Product A product B product A product B none Product A (drop down)
    Target 10 Target 10 Target 10 Target 10 Target _____
    Actual 10 Actual 9 Actual 9 Actual 10 Actual _____
    Loss 0 Loss 1 Loss 1 Loss 0 Loss _____
    Yield 100% Yield 90% Yield 90% Yield 100% Yield _____

    Now its actually a little more complicated than that, as i have 5 machines, on three shifts, but i think once i figure out how to create a formula that basically gives me the total Target, Actual, Loss, and Yield for any given product I make throughout the week I will be good

    thanks in advance, and please let me know if im not explaining it correctly or if there is any more info needed to complete


    -Sam

  2. #2
    Registered User
    Join Date
    04-30-2012
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2013
    Posts
    20

    Re: Drop down lists and autosum

    I see that typing out columns and using the spacebar doesnt really work... here is an uploaded image of what im trying to do. as you can see i can get a weekly total of production, but i want to be able to use a drop down to the right that allows me to select a specific product and see the weekly total of production of just that product. thanks again in advance

    image.jpg

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Drop down lists and autosum

    The function you want is SUMIF.

    In L7, then copied down:

    =SUMIF($B$5:$H$5, $K$6, $B7:$H7)

  4. #4
    Registered User
    Join Date
    04-30-2012
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2013
    Posts
    20

    Re: Drop down lists and autosum

    Awesome! that was exactally what I was looking for!

    But now after messing around for an hour or so trying to link multiple Ranges and trying to see if the SUMIFS formula works, I am having a hard time trying to calculate multiple presses on multiple shifts into that one group of cells that give a total Target, Actual, Loss, and Yield. I have attached another image to explain, but imagine that there are 5 presses each shift, and three total shifts.

    Again, thank you for the response!

    Untitled.jpg

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Drop down lists and autosum

    Your sheet design is what it is. Just create formulas to add each "section", then put the formulas into one big formula.

    =SUMIF($B$5:$H$5, $K$6, $B7:$H7)

    =SUMIF($B$11:$H$11, $K$6, $B13:$H13)


    ...etc.... becomes:


    =SUMIF($B$5:$H$5, $K$6, $B7:$H7) +
    SUMIF($B$11:$H$11, $K$6, $B13:$H13)



    If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
    Last edited by JBeaucaire; 05-01-2012 at 08:27 AM.

  6. #6
    Registered User
    Join Date
    04-30-2012
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2013
    Posts
    20

    Re: Drop down lists and autosum

    Thanks again!

+ 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