# suming a column exlucding the first cell

#### gt9147a

##### New Member
This is something I have been curious about. A lot of tables I create have columns of data which I sum at the top of the column. The amount of data in the column changes over time.

I know that if I put my sum in another column I could use sum(A:A), but I want the total to appear in cell a1.

Since I don't know how much data I will have in the column because I add to it, I currently just use sum(a2:a60000). So far I have never gotten to a60000, so this works out ok. But is there a way to tell excel to sum the entire column excluding cell a1?

#### Yogi Anand

##### MrExcel MVP
gt9147a said:
Can you explain how this function goes about figuring out the end of my range of data? I have tried using the help, but don't really understand how it works.

SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))
Hi gt9147a:

9.99999999999999E+307 is the largest number that can be entered in a cell,

=MATCH(9.99999999999999E+307,A:A)

locates the row number of the last number entered in column A, and

INDEX(A:A,MATCH(9.99999999999999E307,A:A),0) ... with or without ,0

references the last numeric entry in column A.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Replies
11
Views
156
Replies
12
Views
164
Replies
56
Views
480
Replies
10
Views
176
Replies
2
Views
142

1,127,024
Messages
5,622,271
Members
415,890
Latest member
Apopolis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back