44

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?

3
  • 2
    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.
    – Ellesa
    May 21, 2011 at 9:54
  • The best answer to this question is here: superuser.com/a/1259946/342034. Note that I mean specifically the answer I linked to, by BBK. Don't scroll up and look for the accepted answer or the most popular answers. BBK's answer has only two upvotes as of this writing, but it's really the only truly correct answer to this question.
    – Adi Inbar
    Jan 19, 2019 at 10:30
  • Possible duplicate of Selecting whole column except first X (header) cells in Excel
    – Adi Inbar
    Jan 19, 2019 at 10:32

8 Answers 8

22

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.)

1
  • I guess this is the only way to do it if the formula itself is in the same row. Thanks
    – Eddy
    Aug 1, 2011 at 16:58
18

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

5
  • 5
    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. May 17, 2011 at 22:22
  • Just wrap in and "IFERROR" then
    – James
    May 17, 2011 at 22:27
  • Sorry if it was possibly rude how the comment came up. Upvoted now! May 18, 2011 at 14:39
  • 1
    Will this work if the cell with the formula is on the same row, say in A1?
    – Eddy
    Jun 1, 2011 at 13:06
  • 1
    @Eddy No, that would create a circular reference.
    – Adi Inbar
    Jan 19, 2019 at 10:31
11

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.

2
  • 2
    This is actually the best answer. It’s as close to =sum(A3:A*infinity*) as Microsoft Excel will permit. Unfortunately, every reference must be entered twice (one for the OFFSET function and another for the ROWS function).
    – Kevin Li
    Nov 19, 2019 at 21:21
  • This is demonstrably not the best answer as this formula will recalculate if a cell in an unrelated range is changed (i.e. z50). Thus unnecessary calculations are conducted which may not be noticeable for small files, but will can be detrimental with larger file and achieves nothing over this formula which provides the same result: =SUM(A3:INDEX(A:A,ROWS(A:A))) but will not recalc unless a value in columns A is changed. Mar 7 at 16:47
5

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

Edit: Sorry I misread, this is for a column

For rows, see the approved answer.

6
  • 2
    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, 2014 at 16:01
  • 1
    this is actually the correct answer
    – voxobscuro
    Sep 20, 2014 at 13:42
  • 1
    Doesn't like it in Excel for Mac 15.36
    – Will
    Aug 11, 2017 at 15:51
  • For Microsoft Excel 2016, it should be =SUM(A3:A:A).
    – Keith OYS
    Apr 16, 2018 at 12:51
  • In Excel 2013, =SUM(A3:A:A) seems to be the same as =SUM(A:A). (I’m surprised that it isn’t a syntax error.) Oct 23, 2018 at 23:09
4

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.

3

Unfortunately Excel does not have the nifty google sheets feature to write a formula as A3:A (for rows) or C3:3 (for columns). While most of these answers listed will work, I would argue my below formulas are the best technical solutions:

For Rows

=SUM(A3:INDEX(A:A,ROWS(A:A)))

For Columns (excluding A and B)

=SUM(C3:INDEX(3:3,Columns(1:1)))

Why these are better TECHNICAL choices than other listed answers


=sum(a3:a1048576) (accepted answer)

Overall this is a decent solution however this will throw an error in Excel versions 2003 and earlier. It also will not include all rows if future Excel versions increase the row count (next jump would be to 16,777,215 rows if same method from 2003 to 2007). Admittedly, this is quite picky and most users would probably be fine using this solution, however TECHNICALLY it is not as dynamic as my option.


=SUM(A:A)-A1-A2 or =sum(1:1)-sum($a1:$b1)

This works for summations, but this will run into trouble with other functions such as average, count, max, min. My answer will generate the proper range which can then be used for any function that uses a range input.


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

This is similar concept to my answer however Offset is a volatile function that should never be used if you can use index (which we can in this case). It may not make a difference if you have a small file, but using offset can reduce your performance considerably as it constantly recalculates. This article details this issue.

Like I said, all of these will probably address the issue, but I thought I'd just provide some ultra geeked out analysis (hey... what do you expect... it's an Excel question! :)

1

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)
2
  • 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. Feb 1, 2016 at 13:35
  • It would also be nice if that worked because of consistency. If A3:A is a valid range, so should A3:3 be. There must be a syntax reason why it is not allowed in Excel, and Google Sheets designers must have known what that reason was when they got started.
    – Leo
    Oct 18, 2020 at 1:39
0

For what is worth, I wanted to do something similar - to sum all cells in a column between a certain cell and the cell above the place where the sum will appear. This is what I've come up:

=SUM(INDIRECT(ADDRESS(5;COLUMN())&":"&ADDRESS(ROW()-1;COLUMN())))

It is not directly answering OP's question, but it could be helpful to someone.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .