Sum all Values in a changing Column

[RyanH]

I am using VBA to produce a quote worksheet with product descriptions in
column C and the product price in Column F. I want to sum from Cell F6 to
the last product price which varies as products are added and removed from
the worksheet. What formula can I put in the TOTAL cell to show the sum of
all the product prices?

 

[CLR]

=SUM(F6:F65000)

Vaya con Dios,
Chuck, CABGx3

 

[ShaneDevenshire]

Hi Ryan,

If there are no numbers in the cells F1:F5 then you can use:

=SUM(F:F)

 

[RyanH]

I appreciate the reponse, but there is an issue. I failed to mention that
this is a sub-Total cell. I will also have a tax cell, shipping cell, and
TOTAL cell below the sub total. So I am back to my orginal question.

 

[CLR]

Then just limit the Range of cells to those of interest, like

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3

 

[RyanH]

Wow, what a quick response. I wish that solution was that simple, but the
problem with =SUM(F6:F30) is that the rows I want to sum change if a product
is added or removed from the worksheet. For example, now it may be
=SUM(F6:F30) once a product is added it may be =SUM(F6:F39) or =SUM(F6:F15)
if a product is removed.

 

[CLR]

Moving target here..........
With all the details, one could construct a formula that would count the
number of products and sum only their relative cost cells...........

Vaya con Dios,
Chuck, CABGx3

 

[RyanH]

Yes, the moving target does make things difficult. I'm just not sure the
order to use things such as COUNT, OFFSET, SUM, etc. in the formula bar to
get that sum correct sum to show.

 

[ShaneDevenshire]

Please show us a sample of the data.

 

[RyanH]

My worksheet may contain 1 to 9 or so products with costs. But I want to
beable to insert a formula in the Sub-Total cell that will add all the costs.
Cost 1 will always be in cell F6. the Sub-Total cell will change from row
to row as products are added or removed from the worksheet.

For Example:

Col. C Col. F
Product Description 1 Cost 1
Product Description 2 Cost 2
Product Description 3 Cost 3
Product Description 4 Cost 4
--------
Sub-Total
Tax
Shipping
TOTAL

I hope this helps. Thanks for you help!

 

[CLR]

How about putting your =SUM(F6:F15) formula in cell F16 and just leave rows
7-15 available to type in additional products if needed?

Vaya con Dios,
Chuck, CABGx3

 

[RyanH]

I wish I could, but each product description can be 5-15 rows long. When I
print the worksheet, it may be 1 to 5 pages long. So I really need a movable
SubTotal cell.

 

[ShaneDevenshire]

Hi again,

Is the sub total formula cell in column F as are the numbers?

You can do this manually by double-clicking the AutoSum button? Or you can
write VBA. Is the text Sub Total entered in column A to the left of where
you want the calculation?

 

[Harlan Grove]

I wish I could, but each product description can be 5-15 rows long.
When I print the worksheet, it may be 1 to 5 pages long. So I
really need a movable SubTotal cell.

....

OK, you have other calculations below this subtotal cell. This
subtotal cell already exists in your worksheet, no? If so, you'd want
to sum the values in this column from row 6 to the row above this
formula cell. It seems the complication is that you may need to insert
(or delete?) rows as the number of items or the number of rows needed
for their descriptions varies. If so, use a blank row between the last
row of item or description data and the subtotal row. For example, if
the initial layout had item lines in rows 6 to 35, leave row 36 blank
and enter the subtotal formula in row 37 as

=SUM(F$6:F36)

Insert/delete rows below row 6 and above row 36, and this formula will
adjust automatically. This is an ancient spreadsheet technique.

 

[RyanH]

Yes, the SubTotal Calculation should be in Col. F. I was trying to avoid
coding something in VBA, but maybe I will have to. I just wasn't sure if
there was an easier way using the formula bar.

 

[RyanH]

That seems like that would work, but when I run my macros and remove a
product the SubTotal does not calculate automatically. How do I get it to do
that?