Add every few rows within a range

  • Thread starter Thread starter M.Siler
  • Start date Start date
M

M.Siler

Here's a new one for me. I'm trying to find if there is a way to add up
every 14 rows within a range. let me explain further - I'm using row
grouping. The first group is dealers then within the dealers I have
customers. The customer info is the same - 14 lines of data then it repeats.
I have put in 10 customers per dealer. I have 5 dealers. What I like to do
is sum up C16, C30, C44... for all 10 customers. I know I could just write
=C16+C30+C44 etc, but I might have to insert more customers beyond the 10.
What I was thinking if I could some how define dealer #1 is from C8 through
C?? and then have the sum formula look in that rage every 14 rows. If I was
to add customer the range would increase and the formula would adjust. I
hope I explained this well enough and I hope there is a solution.
 
Here's a new one for me. I'm trying to find if there is a way to add up
every 14 rows within a range. let me explain further - I'm using row
grouping. The first group is dealers then within the dealers I have
customers. The customer info is the same - 14 lines of data then it repeats.
I have put in 10 customers per dealer. I have 5 dealers. What I like to do
is sum up C16, C30, C44... for all 10 customers. I know I could just write
=C16+C30+C44 etc, but I might have to insert more customers beyond the 10.
What I was thinking if I could some how define dealer #1 is from C8 through
C?? and then have the sum formula look in that rage every 14 rows. If I was
to add customer the range would increase and the formula would adjust. I
hope I explained this well enough and I hope there is a solution.
Click to expand...

Is this an existing project or one you're creating? How many dealers are
there?
How about one sheet per dealer and a summary page with links? Keep the
items you want to sum in a column by itself and =sum(C:C). That way you
don't need a range.

Jordon
 
You could create non-contiguous Ranges, and sum each one
individually.........

Vaya con Dios,
Chuck, CABGx3
 
I'm not quite sure what you're doing, but you may want to look at pivottables.

Add a headerrow to your data:

Dealer customer Amount???

Then put all your data in. No blanks rows at all.

Then select this range and do
Data|Pivottable
follow the wizard until you get to the step that has a Layout button on it.
click on that button.

Drag customer to the row field
drag dealer to the row field
drag amount to the data field
(if it shows Count of, double click on it and make it Sum of.)

Follow the wizard to the end.

These grey "buttons" are hot. You can double click on them and hide subtotals.

You can drag them to the right/left and "pivot" your data -- get a different
summary.

With a little practice, you'll find these things really useful.

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/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
 
Back
Top