Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / January 2008

Tip: Looking for answers? Try searching our database.

Sum all Values in a changing Column

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RyanH - 02 Jan 2008 20:16 GMT
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?
Reply to this Message
CLR - 02 Jan 2008 20:25 GMT
=SUM(F6:F65000)

Vaya con Dios,
Chuck, CABGx3

> 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?
Reply to this Message
ShaneDevenshire - 02 Jan 2008 20:38 GMT
Hi Ryan,

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

=SUM(F:F)

Signature

Cheers,
Shane Devenshire

> 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?
Reply to this Message
RyanH - 03 Jan 2008 15:11 GMT
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.  

> Hi Ryan,
>
[quoted text clipped - 7 lines]
> > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > all the product prices?
Reply to this Message
CLR - 03 Jan 2008 15:19 GMT
Then just limit the Range of cells to those of interest, like

=SUM(F6:F30) for example

Vaya con Dios,
Chuck, CABGx3

> 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
[quoted text clipped - 11 lines]
> > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > all the product prices?
Reply to this Message
RyanH - 03 Jan 2008 15:25 GMT
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.

> Then just limit the Range of cells to those of interest, like
>
[quoted text clipped - 18 lines]
> > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > all the product prices?
Reply to this Message
CLR - 03 Jan 2008 15:30 GMT
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

> 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
[quoted text clipped - 24 lines]
> > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > all the product prices?
Reply to this Message
RyanH - 03 Jan 2008 15:35 GMT
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.

> Moving target here..........
> With all the details, one could construct a formula that would count the
[quoted text clipped - 31 lines]
> > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > all the product prices?
Reply to this Message
ShaneDevenshire - 03 Jan 2008 16:20 GMT
Please show us a sample of the data.
Signature

Thanks,
Shane Devenshire

> 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
[quoted text clipped - 35 lines]
> > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > all the product prices?
Reply to this Message
RyanH - 03 Jan 2008 17:46 GMT
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!

> Please show us a sample of the data.
>
[quoted text clipped - 37 lines]
> > > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > > all the product prices?
Reply to this Message
CLR - 03 Jan 2008 18:00 GMT
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

> 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.
[quoted text clipped - 57 lines]
> > > > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > > > all the product prices?
Reply to this Message
RyanH - 03 Jan 2008 18:12 GMT
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.

> 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?
[quoted text clipped - 63 lines]
> > > > > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > > > > all the product prices?
Reply to this Message
ShaneDevenshire - 03 Jan 2008 18:43 GMT
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?

Signature

Thanks,
Shane Devenshire

> 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
[quoted text clipped - 67 lines]
> > > > > > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > > > > > all the product prices?
Reply to this Message
RyanH - 04 Jan 2008 16:18 GMT
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.

> Hi again,
>
[quoted text clipped - 75 lines]
> > > > > > > > > > > > the worksheet.  What formula can I put in the TOTAL cell to show the sum of
> > > > > > > > > > > > all the product prices?
Reply to this Message
Harlan Grove - 03 Jan 2008 19:24 GMT
RyanH <Ry...@discussions.microsoft.com> 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.
Reply to this Message
RyanH - 04 Jan 2008 16:31 GMT
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?

> RyanH <Ry...@discussions.microsoft.com> wrote...
> >I wish I could, but each product description can be 5-15 rows long.
[quoted text clipped - 16 lines]
> Insert/delete rows below row 6 and above row 36, and this formula will
> adjust automatically. This is an ancient spreadsheet technique.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.