Help with setting up a formula

[Greggo G]

Hi. I need some help setting a formula for the following:

Info on Sheet1

Operation Item A Item B Item C
1.Cut 10 min 12 min 15 min
2.Trim 22 min 19 min 17 min
3. Polish 13 min 14 min 16 min

Info on Sheet2
01/11 02/11 03/11
Item A 9 units 5 units 5 units
Item B 0 units 2 units 3 units
Item C 0 units 2 units 1 unit

Info required on Sheet3
01/11 02/11 03/11
1. Cut (tot min) (tot min) (tot min)
2. Trim (tot min) (tot min) (tot min)
3. Polish (tot min) (tot min) (tot min)

Sheet1 contains the info for each operation for the different items
Sheet2 contains a schedule of how many per day of each item is built
Sheet3 must contain a daily summary of operations times per item x build
schedule.

Let me know if you need more info and I'll forward you a spreadsheet if have
been working on.

Thank you & kind regards
Greg

 

[Ms-Exl-Learner]

Hi Greg,

Instead of mentioning the required total Minutes as (tot min) in the below
example, just mention the Total Minutes (Values) which should be retrieved
using the formula for our easy reference.

Info required on Sheet3
01/11 02/11 03/11
1. Cut (tot min) (tot min) (tot min)
2. Trim (tot min) (tot min) (tot min)
3. Polish (tot min) (tot min) (tot min)

 

[Dave Peterson]

I would lay out those tables differently.

On Sheet1:

Column A ColB ColC (in minutes)
Cut ItemA 10
Trim ItemA 12
Polish ItemA 15
....

Each item/operation has its own entry/row.

On Sheet2:

ColA ColB (full date) ColC (Units)
ItemA 01/11/2010 9

.....

Each Item/date has its own entry/row.

And then I'd add more columns to Sheet2...

ColA ColB (full date) ColC (Units) ColD(Cut) ColE(trim) ColF(polish)
ItemA 01/11/2010 9 (Time) (time) (Time)

Each of these additional columns would contain formulas that determine that
value.

For the Cut column with "Cut" in D1
=sumproduct(--(sheet1!$a$1:$a$99=d$1),
--(sheet1!$b$1:$b$99=$a2),
sheet1!$c$1:$c$99)

(more on this formula later)

And drag to the right for Trim and Polish and drag all 3 formulas down as far as
you need.

This will build a table that you can use to create a pivottable that looks like
the layout you want.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

================

The =sumproduct() formula...

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And if you're using xl2007, take a look at =sumifs() in xl's help. The formula
may be easier to understand.