Stumped: How to make a 'totals' column in Excel?

[Chris2wire]

ive searched and searched the entire interweb and nothing...

I need to a column in Excel act as a totals column that will automatically multiply two other columns in it. Therefore when the next row is completed this column, but on the next row, will automatically make a total.

Argh anyone know how??

 

[Markbnj]

Just click the formula button (up at the left side of the cell edit box I think, on the toolbar), click 'Sum' in the drop down list, then select the individual cells or ranges of cells that you want totalled. If you need to sum intermediate results, i.e. a sum of sums, then you can use hidden cells for the intermediate column totals and then use a visible cell to sum those. It's pretty basic, and you'd probably figure it out just by playing with it, so perhaps I am not understanding the problem.

 

[Matthias99]

The OP mentioned 'multiplying' the other data... in that case, you'd want to use PRODUCT() rather than SUM(), but otherwise it basically works the same.

ie, if you have values in A1 and B1 and you want C1 to always contain A1*B1, you can put either of the following in C1:

=A1*B1

OR

=PRODUCT(A1:B1)

(or use the function wizard to do it for you, or type '=PRODUCT(' and then highlight the cells you want multiplied together)

If you then copy/paste that value in the rest of the cells in column C, it would automatically adjust (so in C2, you'd get '=PRODUCT(A2:B2)', etc.)

 

[Chris2wire]

When I copy and paste it on down through the column, it keeps the exact same formula and does not adjust for it being the next row...

Im stumped.

 

[Matthias99]

Originally posted by: Chris2wire
When I copy and paste it on down through the column, it keeps the exact same formula and does not adjust for it being the next row...

Im stumped.

If you used the formula wizard, make sure it didn't 'lock' the entries. That is, it should be:

=PRODUCT(A1:B1)

and not

=PRODUCT($A$1:$B$1)

(or whatever formula it should be)

What version of Excel is this exactly?

Also, this is not an OS question... should be in Software.

 

[Dave Richardson]

Having a bit of trouble understandin exactly what you want. Is it a cumulative total?
If so the only trick is to make the first data row right and copy it down. It will involve working with a dummy row with nothing in it.

a1 b1 c1 d1 Just leave all blank (Dummy row)

a2 b2 c2 d2 First row of data

a3 b3 c3 d3 Second row of data

an bn cn dn nth row of data


Assuming a2 to an is data field
Assuming b2 to bn is data field
Assuming c2 to cn is a derived value from a2 to an and b2 to bn
Assuming d2 to dn is the column of cumulative totals

then d2 is =d1+c2
then d3 onwards is just a copy d2 pasted into the rest of the range. fornulae adjust automatically.
ie d3 is d2 + c3
ie dn is d(n-1) + cn

Column c can be hidden if required.

Anyway - that the way I do it.

 

[JW310]

Originally posted by: Chris2wire
When I copy and paste it on down through the column, it keeps the exact same formula and does not adjust for it being the next row...

Im stumped.

Try using the "fill down" feature to copy the formula down the column, instead of copying and pasting. Select the cells in the column that you want to copy the formula to (starting with the cell that has the formula in it), and then press Ctrl+D. It should copy the formula down through the selected cells and update cell references to take into account the formula being in different rows.

JW

 

[The J]

Originally posted by: Chris2wire
When I copy and paste it on down through the column, it keeps the exact same formula and does not adjust for it being the next row...

Im stumped.

If I remember correctly, there should be a small black square on the lower-right of the currently selected column. Rather than doing a copy-paste, try dragging that square down through the rows you need. That should adjust everything for you.