Excel 2007 : Drop down lists and autosum

[slaters]

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

[slaters]

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

[JBeaucaire]

The function you want is SUMIF.

In L7, then copied down:

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

[slaters]

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

[JBeaucaire]

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.

[slaters]

Thanks again!