suming a column exlucding the first cell

gt9147a

New Member
Joined
Nov 26, 2005
Messages
3
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?
 

Some videos you may like

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
Joined
Sep 3, 2002
Messages
10,473
You can create a dynamic range via

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

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
=SUM(A2:INDEX(A:A,MATCH(9.99999999999999E307,A:A),0))
Fix for missing parenthesis
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Nov 26, 2005
Messages
3
THanks guys, you have answered a question I have been wondering about for several years.
 

gt9147a

New Member
Joined
Nov 26, 2005
Messages
3
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,458
Members
412,595
Latest member
slim313
Top