Reply to thread

Message

[QUOTE="Dave Peterson, post: 3263138"]
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:
[URL]http://peltiertech.com/Excel/Pivots/pivottables.htm[/URL]
And Debra's own site:
[URL]http://www.contextures.com/xlPivot01.html[/URL]

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

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

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

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

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:
[URL]http://www.xldynamic.com/source/xld.SUMPRODUCT.html[/URL]

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

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

Verification