DATA/SUBTOTALS

[gary]

How can I use SUBTOTALS to get BOTH column totals AND item-
counts?

For example:

Part Nbr Amount
39495 100.00
39495 15400.00
39495 10.00
39395 650.00
40000 1000.00
40000 1200.00
40000 30.00
54050 600.00
54050 1200.00
54505 2.00
54505 300.00
54505 50.00
54505 10.00

 

[gary]

How can I get the item-count to be ABOVE the first
occurrence of each part-number and the column-total to be
BELOW the last occurrence of each part-number?

Something like this:

Part-nbr 39495 - count = 4
39495 100.00
39495 15400.00
39495 10.00
39395 650.00
Part-nbr 39495 - total = 16160.00

Part Nbr 40000 - count = 3
40000 1000.00
40000 1200.00
40000 30.00
Part Nbr 40000 - total = 2230.00

Part Nbr 54050 - count = 2
54050 600.00
54050 1200.00
Part Nbr 54050 - total = 1800.00

Part Nbr 54505 - count = 4
54505 2.00
54505 300.00
54505 50.00
54505 10.00
Part Nbr 54505 - total = 362.00

Gary

 

[Dave Peterson]

You could do this kind of thing with a macro, but I wouldn't waste my time. If
you take advantage of the built in features, you'll find that your life is much
easier.

I'd do a data|subtotals on both columns. Use Sum and "add subtotal" to both
fields (you'll fix the first field later).

When you click ok, you'll see a new column A and the part number's will be
summed up (but it's meaningless (or 0 if your part numbers were text).)

But select column B (the part number column) and do:
Edit|Replace
what: (9,
with: (3,

The =subtotal(9,A2:Ax) (which sums)
will become
=subtotal(3,a2:ax) (which counts)

===
Another option you may want to consider is Data|pivottables. You can add a
count of part numbers and sum of Amounts pretty darn quickly. (It won't show
the details, however.)

 

[gary]

Dave,

(Boy, you're good!).

But, is there a way to get the count to appear on the row
ABOVE the first occurrence of the part-number?

In my workbook, each part-number has between 1 and xxx rows
and has 15 columns. (My workbook has 10,000+ rows).

My plan is to search for a particular count (like "11") and
paste a "block" of formulas into the cells that that count
applies to. I'd then repeat this search-and-paste process
for all of count-'11' cells.

I'd then search for a different count (like '7') and paste
an appropriate "block" of formulas for count-"7" cells.

I'd then do the same for all of the other counts.

(I know this means "searching-and-pasting" for every
occurrence of every count but I don't know a better way.
Do you?)

Gary

-----Original Message-----
You could do this kind of thing with a macro, but I wouldn't waste my time. If
you take advantage of the built in features, you'll find that your life is much
easier.

I'd do a data|subtotals on both columns. Use Sum and "add subtotal" to both
fields (you'll fix the first field later).

When you click ok, you'll see a new column A and the part number's will be
summed up (but it's meaningless (or 0 if your part numbers were text).)

But select column B (the part number column) and do:
Edit|Replace
what: (9,
with: (3,

The =subtotal(9,A2:Ax) (which sums)
will become
=subtotal(3,a2:ax) (which counts)

===
Another option you may want to consider is

Data|pivottables. You can add a

 

[Dave Peterson]

You could write a macro that would count each group and put a "header" row for
each group.

But there might be an easier way to accomplish what you want.

You may want to post more details to see if anyone has a good idea.

You may even want to post in a new thread (with more details). The longer
threads get, the better chance that it'll be overlooked by lots.

 

[Ragdyer]

<<"The longer threads get, the better chance that it'll be overlooked by
lots.">>

Kind of disagree there Dave.

Scrolling through the group and seeing a long thread *does* pique the
curiosity of, "What's going on here that I don't know about".

Case in point ... Norman's "Fourth of July" thread.

 

[Ragdyer]

In the <Data> <Subtotals> dialog window, you'll see a choice of "Summary
Below Data".

If you *uncheck* that box, you'll get your totals *above* the individual
groups.

 

[gary]

To keep this simple:

A1 thru A15 contains "Part 1".
B1 thru B15 contains the price of Part 1.
A16 contains the count of Part 1. (That count is 15).
B16 contains the total of Part 1's prices.
C1 contains =B1/$B$16.
C2 contains =B2/$B$16.
C3 contains =B3/$B$16.
....
C15 contains = B15/$B$16.

(The C1 thru C15 "block" contains 15 formulas (which is the
count in A16).


A17 thru A23 contains "Part 2"
B17 thru B23 contains the price of Part 2.
A24 contains the count of Part 2. (That count is 7).
B24 contains the total of Part 2's prices.
C17 contains =B17/$B$24.
C18 contains =B18/$B$24.
C19 contains =B19/$B$24.
....
C23 contains =B23/$b$24.

(The C17 thru C23 "block" contains 7 formulas (which is the
count in A24).


A25 thru A39 contains "Part 3".
B25 thru B39 contains the price of Part 3.
A40 contains the same count as Part 1. (That count is 15).
B40 contains the total of Part 3's prices.

Because the counts for Part 1 and for Part 3 are the same
(15), I want to paste the "block" of 15 formulas (in C1
thru C15) into B25 thru B39. And repeat this for every
part-number whose count = 15.

 

[gary]

But can I get the count ABOVE each individual group and the
total-amount BELOW each individual group?

 

[Norman Harker]

Hi RD!

Agreed! I always take a look at long threads; "Who called the party
and didn't invite me?" But more to point it is often a discussion of
issues or covering a point of difficulty. Many cases in the past and I
can think of some involving unary minus and MIRR that have been very
valuable discussions.

And sometimes there's some pretty good humour for those living
restricted lives.

 

[gary]

Using DATA/SUBTOTALS/SUM on the "Part Nbr" column and on
the "Amount" columns provides the total amounts for each
column.

Dave's suggestion -- replace "(9" with "(3" on the "Part
Nbr" column -- provides the count for each Part Nbr.

Ragdyer's suggestion -- to uncheck the "Summary Below Data"
box -- puts both the count and the total amounts ABOVE each
Part Nbr's data.

I can then find all of the counts with the same value and
then paste the appropriately-sized formula "block".

 

[Dave Peterson]

You could use =countif() to count each part number.

And if you wanted to divide each part by its associated count, you could put
something like this in C2:

=B2/COUNTIF(A:A,A2)

I really hate to insert additional rows into my data. It usually makes it more
difficult to manipulate that data--subtotals, sorting, pivottables and even just
autofilters become a pain.