How to Total a Column

[JohnBates]

Hi Excel experts !

My question is so basic I'm embarrassed to have to ask... but here goes.

My Excel 2000 worksheet has:

Item # Warehouse Quantity
123 1 25
123 2 25
123 Total 50

124 (we start a new Item # here..)


My problem: When the user changes a Quantity, the Quantity
value in the Total row does not change to reflect the new sum.

I can get it to total by using a hard-coded formula...
= SUM(X3:X5) but this is not really going to work - The number of Items is ever-changing... this Item will not always be in only 2 warehouses.

Note: I do NOT want a separate row that contains the total. The user wants to see the new total in the 'Total' row.

Can anyone help this novice?
Thanks, John

 

[chifu]

There should not be any problem with using the SUM function for this. As the user changes a Quantity the formular should automatically update the SUM total. (unless you have turned off automatic updating, is that why you say you can not use SUM?)

Also you should be able to add and delete rows as long as you add them in between two exsiting item rows, else you will find the formular will not change for the row addition.

Unless you keep changing the number of warehouses you have, or you have a large number of warehouses why not put all the warehouses in and only change the quantity and zero the ones that do not have an item.

The otherway to do this is to have a separate column for each warehouse you have. Then you only need one row for each item and the total would be at the end or beginning of the row. This I think would be better as it makes adding scenario formulars and reporting much easier, but you have to decide as we do not know what you use the spreadsheet for.

 

[Hasit]

The other option you have is to create a NAMEd region. So the column which has quantities can be a named region, and you can then SUM based on the named region.

For example, if you create a named region called TEST. TEST can relate to cells $C$1:$C$100. The function in the TOTAL row as you define it can be =SUM(TEST)

Any numbers you add to any of the cells that fall into the named region (C1:C100) will automatically be added to the total.

p.s. to create a named region, highlight the region you want to name, then hit CTRL-F3, type in a NAME, click on ADD and then OK.

 

[JohnBates]

Thanks for your reply chifu.

I didn't explain my question very well.

This worksheet has a macro that pulls-in the initial values and formats and totals. This is working well.... until the user modifies the Quantity. The Quantity total in the Total line (which is pre-built by the macro) is not changing to reflect the modified quantities for the items in that group.

No, Automatic updating is not turned off.

But I need for the SUM to apply to all Item numbers.
Do I have to manually insert the = SUM(X..:X..)
for the Quantity cell in every Total line in the worksheet?
There are hundreds of Item #s, so thee are hundreds of Total lines.

I must be making this more difficult than it should be.

Thanks, John

 

[dsi]

You can use the Sheet_Calculate event to re-run the macro every time the sheet is updated.

You can also use a pivot table to automatically display the overall data, rather than looking at the data sheet. You can have the data on one sheet (like you have it), except remove the "Total" row. Then, build a pivot table that displays the item number, quantities in each warehouse and a total for that item. This would also eliminate the need for a macro and would solve all of your totalling problems. The pivot table would also make it much easier to read your data.

Data Sheet
Item Whse Qty
123 1 25
123 2 25
124 1 10
124 2 15
124 3 5

Pivot Table (on a new sheet for a good visual)
Item Whse1 Whse2 Whse3 Total
123 25 25 0 50
124 10 15 5 30

Hope this helps...

 

[SkipVought]

Hi,
ANother solution is to use the SubTotal feature --
Menu Item - Data/Sub Totals...

You will need to resort by ITEM before applying subtotals

Skip,
metzgsk@voughtaircraft.com