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?

=SUM(F6:F65000)

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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?

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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!


"ShaneDevenshire" wrote:

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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" wrote:

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!


"ShaneDevenshire" wrote:

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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.

"CLR" wrote:

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" wrote:

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!


"ShaneDevenshire" wrote:

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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?

--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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!


"ShaneDevenshire" wrote:

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

RyanH wrote...
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.

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.

"ShaneDevenshire" wrote:

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?

--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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!


"ShaneDevenshire" wrote:

Please show us a sample of the data.
--
Thanks,
Shane Devenshire


"RyanH" wrote:

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.

"CLR" wrote:

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" wrote:

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" wrote:

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

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3



"RyanH" wrote:

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.

"ShaneDevenshire" wrote:

Hi Ryan,

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

=SUM(F:F)


--
Cheers,
Shane Devenshire


"RyanH" wrote:

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?

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?

"Harlan Grove" wrote:

RyanH wrote...
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.