# Excel: How do I reference an entire row except for a couple of cells?

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?

-
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 '11 at 9:54

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

-
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

-
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

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