# 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?

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

#### just_jon

##### Legend
You can create a dynamic range via

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

#### babycody

##### Well-known Member
=SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))
Fix for missing parenthesis

#### Yogi Anand

##### MrExcel MVP
Book1
ABCD
148
2148
32
43
54
65
76
87
98
10
1112
12
Sheet2

formula in cell C2 is ... =SUM(A:A)-A1

#### Yogi Anand

##### MrExcel MVP

Or if you want to have the sum in cell A1, then ...

Jon's formula ... =SUM(A2:INDEX(A:A,MATCH(9.9E+307,A:A)))

#### just_jon

##### Legend
babycody said:
=SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))
Fix for missing parenthesis

Thanks for that. #### Yogi Anand

##### MrExcel MVP

Hi Jon:

in ... =SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))

why the ,o? the formula does work with out the ,0 ... just curious!

#### just_jon

##### Legend
Yogi Anand said:
Hi Jon:

in ... =SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))

why the ,o? the formula does work with out the ,0 ... just curious!

Just force of habit, I guess. When I first learned the formula, I didn't know it made no difference, and when I found out, well, can't teach an old dog every new trick...

#### gt9147a

##### New Member
THanks guys, you have answered a question I have been wondering about for several years.

#### gt9147a

##### New Member
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))

Replies
5
Views
111
Replies
3
Views
73
Replies
3
Views
139
Replies
25
Views
231
Replies
0
Views
97