Microsoft Excel Headache!?

Question

I have a Microsoft Excel spreadsheet I am trying to extract data from. I want to make a column that gives the sum of values from only certain columns accross a row. I want to do this for many rows, so am looking for a way to do this without having to manually enter each cell individually into an equation.

For example, I am looking only to add, say, the data from columns D,F,H,J,L,N,P,and R, all from cells from the same row. Is there a way to do this with a generic equation, so I do not have to put D3,F3,H3,J3, etc.? I have too many rows to do by manual equation writing. Surely there is an easier way!?

Thanks so much for your help!

Answer 1

Yes, assuming the data is always in columns D,F,H,J,L,N,P, and R. Go to the cell in the first row you want to add these together.

Type the formula, then grab the fill handle and drag it down for all the rows. This will copy your formula and make all the row changes automatically. (The fill handle is the very small black + in the bottom right corner of the active cell.)

Takes less time to do this than reading this answer.

Answer 2

Actually this is pretty easy. Go to the top cell that you want to put your equation in and type:

=SUM(

Then in that row hold down the Ctrl key and select the cells that you want it to sum up while keeping the Ctrl key held down the enter time. Once you are done picking all of those cells, let up the Ctrl key and enter ) to finish the equation.

Now all you should have to do is copy and paste that formula down the column unless there is an absolute reference that you are referencing to. By that I mean, that every formula in that column you want to reference to that one cell, say A1.

To use absolute referencing, just put a dollar sign in front of the section you don't want to change. With relative referencing, the cells in the formula will change based on which way you copy the formula. So in row three it might reference:

D3,F3,H3,J3, etc

And in row four with relative referencing, it would reference:

D4,F4,H4,J4, etc

If you put a dollar sign in front of the row portion of the cell reference, the row won't change. Say if I started in row 3 and copied that formula down to row 4 and I used absolute referencing for the row portion on the value in Column F it would look like:

D4,F$3,H4,J4, etc

Another way instead of copying and pasting the cells down is to drag fill the cells. Put your cursor in the bottom right corner of the cell you created the formula in. When it turns to a black cross, click and hold down the mouse button and drag your cursor down. That will fill in the cells below with the modified version of your formula.

If you don't like typing the dollar signs in for any absolute reference you'd like to make, click your cursor in front of the cell reference that you want to make an absolute reference. Then make sure you let off the Ctrl key if you were using that and press the F4 key. This will cycle through the different types of references for that cell reference. If you did this for cell reference A3, you'd get:

A3, $A$3, A$3, and $A3

As you cycled through by hitting the F4 key.

Answer 3

This is simpler than it might at first appear. You need to create only one actual formula summing the sparse columns that you have. for example:
=SUM(H6+E6+C6)

Then, assuming that either EVERY row,needs to be similarly
summed,you need only copy and paste the original formula
into the new locations and Excel (and most other spreadsheets) will adjust the row to match up.

if you need to sum every row, you can copy once,resulting
in two rows in a row. Then select those two cells and
paste them again,resulting in 4 cells, and it geometrically
increases,.so that by the time you've reached halfway
thru all of the rows you need to copy/paste,there is only
one more paste operation left to do.

Good luck with it!