# 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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You can create a dynamic range via

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

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

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

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

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

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

Thanks for that.

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!

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

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

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
3
Views
124
Replies
4
Views
105
Replies
4
Views
76
Replies
1
Views
182
Replies
2
Views
93

1,212,151
Messages
6,106,259
Members
448,009
Latest member
wbarkwell

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