suming a column exlucding the first cell

[gt9147a]

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?

 

[just_jon]

You can create a dynamic range via

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

 

[babycody]

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

 

[Yogi Anand]

How about ...

Book1
	A	B	C	D
1	48
2	1		48
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10
11	12
12
Sheet2

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

 

[Yogi Anand]

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]

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

Thanks for that.

 

[Yogi Anand]

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]

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]

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

 

[gt9147a]

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