Subtotal - SUM, COUNT NUMBER, 'Live' Formula

[RJB]

OK, so I have a list of orders.

Some orders have just one row of data, some have two rows of data,
some have three, some have four, etc.

Each row represents either a delay or an activity. (So, obviously, an
order can have more than one of either.)

I need to aggregate into one row:
Order | Type of Order | Num. of Delays | Total Length of Delays

HERE'S THE MANUAL WAY I'M DOING THIS NOW:
======================================
My initial columns are:

Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D)

I added a "marker column" - Delay Length (E):
=IF(C= a delay, D, "Activity")
In other words, if the Action Code indicates a delay, put the length
of delay in cell E. Otherwise, put in text.

Then I click the subtotal button, At Each Change in Column 'Order
Number (A)', Use Function.... SUM to Delay Length (E). Then I click
it again, NOT replacing existing, and Use Function... COUNT NUMBER to
Delay Length (E).

So that gives me a table of
* First Work Order
- Number of delays
- Sum of delay time
* SecondWork Order
- Number of delays
- Sum of delay time
* Nth Work Order
- Number of delays
- Sum of delay time

THEN I copy Visible Cells and post into another worksheet and then
start manipulating.




So.... Is there a spiffy way to do this with formulas? I could crack
this if every order had the same number of rows, but it does NOT.











-----
EXAMPLE DATA TABLE
Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D) | Delay Length (E)

111 | Rebuild | Delay | 0:10 | 0:10
111 | Rebuild | Shipping | 0:15 | Activity
112 | New | Manuf | 0:50 | Activity
112 | New | Packing | 0:40 | Activity
112 | New | Delay | 0:12 | 0:12
113 | Scrub | Prep | 0:20 | Activity
113 | Scrub | Delay | 0:10 | 0:10
113 | Scrub | Delay | 0:16 | 0:16
113 | Scrub | Packing | 0:05 | Activity
113 | Scrub | Billing | 0:07 | Activity

EXAMPLE SUBTOTAL
Order Number (A) | Type of Order (B) | Action Code (C) | Action Length
(D) | Delay Length (E)
111 Count 1
111 Total 0:10
112 Count 1
112 Total 0:12
113 Count 2
113 Total 0:26


What I'd like?
A worksheet (can I pivot this?) where it automagically takes my data
table and presents/reads:

111 | Rebuild | 1 | 0:10
112 | New | 1 | 0:12
113 | Scrub | 2 | 0:26

 

[ozgrid.com]

If your data is set-up correctly you can use a PivotTable or Subtotal
feature;
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/subtotal.htm

 

[RJB]

If your data is set-up correctly you can use a PivotTable or Subtotal
feature;http://www.ozgrid.com/Excel/excel-pivot-tables.htmhttp://www.ozgrid.com/Excel/subtotal.htm

I forgot to mention it's 58,000 rows...

 

[RJB]

If your data is set-up correctly you can use a PivotTable or Subtotal
feature;http://www.ozgrid.com/Excel/excel-pivot-tables.htmhttp://www.ozgrid.com/Excel/subtotal.htm

I don't see what in the Subtotal link you sent is any different from
what I'm doing...?

As for Pivots, I showed how my data was set up... Do you think a pivot
table would work? I'm also going to experiment with Access; it's a lot
of data. Access was having trouble reconciling data and text in same
column, and only doing math on the data and skipping the text - which
is easy-peasy for Excel.

 

[Dave Peterson]

It looks like a pivottable should work ok to me.

But it sounds like you haven't tried it (yet).

There may be formulas that accomplish the same thing (=countifs(), =sumifs() or
=sumproduct()), but I wouldn't want to use them with data this large. I find
that pivottables calculate faster and only on demand (refreshed by clicking a
button).

You may want to experiment with pivottables with a subset of your data -- just
to make it easier to try things.

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

 

[RJB]

It looks like a pivottable should work ok to me.

But it sounds like you haven't tried it (yet).

There may be formulas that accomplish the same thing (=countifs(), =sumifs() or
=sumproduct()), but I wouldn't want to use them with data this large.  I find
that pivottables calculate faster and only on demand (refreshed by clicking a
button).

You may want to experiment with pivottables with a subset of your data --just
to make it easier to try things.

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/downloa....com/assistance/2002/articles/xlconPT101.aspx

Thanks, it's been years since I'd used Pivots, so before I dug in and
got frustrated, wanted to make sure that was the right direction.

I will practice first, of course!