Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to be able to sum together the values of an entire row except for the first 2 cells. So something like this:

=sum(A3:A*infinity*)

How do I do this?

share|improve this question
    
You may need to edit your question or your example. You wish to reference an entire row but your example sums a column of data. – Kaze May 21 '11 at 9:54
up vote 10 down vote accepted

Something similar has been asked before and I'll repeat this answer: just put:

=sum(a3:a1048576)

because that's the max number of cells per column / row in Office 2007 and 2010. (65536 is max for before 2007.) (For columns, the max column name is XFD in 2007 and 2010, and IV before 2007.)

share|improve this answer
    
I guess this is the only way to do it if the formula itself is in the same row. Thanks – Eddy Aug 1 '11 at 16:58

Add up everything and then take away the ones you don't want to include:

=SUM(A:A)-A1-A2

Edit:

As josmh has pointed out, the formula above will fail with an error if A1 or A2 are non numeric - but the following will work:

=SUM(A:A)-SUM(A1,A2)  

or

=SUM(A:A)-SUM(A1:A2)  

or (for a bit of fun!)

=SUM(A:A,(-1*(SUM(A1))),(-1*SUM((A2))))

These work because the SUM function ignores non-numeric fields

share|improve this answer
1  
This will not work if A1 or A2 are headers that hold text such as "Number of People" or "Price". Subtracting "Price" from a number causes a formula error. – Joseph May 17 '11 at 22:22
    
Just wrap in and "IFERROR" then – James May 17 '11 at 22:27
    
Thanks @josmh - good point. Revised formulas added. – Linker3000 May 17 '11 at 22:41
    
Sorry if it was possibly rude how the comment came up. Upvoted now! – Joseph May 18 '11 at 14:39
    
Will this work if the cell with the formula is on the same row, say in A1? – Eddy Jun 1 '11 at 13:06

I thought you could simply do the following: =SUM(A3:A)

share|improve this answer
1  
I remember doing this in the and it worked in a past job I had, but in my copy of Office 2007 right now that notation doesn't work in formulas, or if you type it into the Name Box. – p0lar_bear May 2 '14 at 16:01
    
this is actually the correct answer – voxobscuro Sep 20 '14 at 13:42

To sum an entire row:

=sum(1:1)

Replace 1 with your starting row number and then use the autofill handle accordingly. If you drag the formula down, it'll change to "=sum(2:2)" and so on..

To sum the entire row, except the first 2 columns:

=sum(1:1)-sum($a1:$b1)

This'll help you cut down on the file size.

share|improve this answer

This already has several fine answers but here's a method I haven't yet seen posted:

=SUM(OFFSET(3:3,0,2,1,COLUMNS(3:3)-2))

The format is SUM(OFFSET(Range, Rows, Cols, [Height], [Width])). We start with the entire range, offset it zero rows down and two columns over, set the height to 1, and set the width to its current width minus two. So long as the width adjustment is the same as the offset, it works perfectly. Here's the same idea to sum a column instead of a row: (Notice that you don't have to use the Width parameter because you want the same width as the original range.)

=SUM(OFFSET(A:A,2,0,ROWS(A:A)-2))

The benefit of these formulas over the currently accepted answer is that they work in any version of excel. It also won't adjust the range if you insert cells in front because it's referencing the entire row / column. You may or may not want that functionality depending on your data setup.

share|improve this answer

You can also select cells in a row or column by selecting the first cell and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.

PS.
Is a pity that Excel does not have the same trick that Google Sheets has. By eg. to select from C2 to C (infinity), you can do there:

SUM(C2:2)
share|improve this answer
    
How sure are you that this works? It sure doesn't work in Excel 2013. Also, this is the same answer that Skube posted over 2 years ago. – Engineer Toast Feb 1 at 13:35
    
fixed, thanks for obs – Serge Feb 2 at 11:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.