I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

=sum(a1,a4)+sum(a7,a9)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jmcclain" wrote in message
...
I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

One way:
=sum(30 cells)+sum(30 more cells)+....

You may want to consider adding a helper column (new column A???).

You could put an indicator (x) in each row that should have it's corresponding
value in column B added:

=sumif(a:a,"x",b:b)

You could hide this helper column when you were done making the entries.



jmcclain wrote:

I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

--

Dave Peterson

Thanks greatly -

"Don Guillett" wrote:


=sum(a1,a4)+sum(a7,a9)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jmcclain" wrote in message
...
I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

Thanks greatly...

"Don Guillett" wrote:


=sum(a1,a4)+sum(a7,a9)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jmcclain" wrote in message
...
I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

=SUM((more,than,30,cells))

Note the double parens.


Gord Dibben MS Excel MVP


On Mon, 12 Oct 2009 14:02:01 -0700, jmcclain
wrote:

I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

Hi,

Can the double bracket technique be used to over come the 29 cell reference
limit for any function?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=SUM((more,than,30,cells))

Note the double parens.


Gord Dibben MS Excel MVP


On Mon, 12 Oct 2009 14:02:01 -0700, jmcclain
wrote:

I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

Not "every" function for sure but I don't have enough testing done to know
which.

Most of the mathematical functions like AVERAGE and COUNT take more than 30
args with the double parens.

When you have tried them all Ashish, post the list<g


Gord

On Thu, 15 Oct 2009 04:48:39 +0530, "Ashish Mathur"
wrote:

Hi,

Can the double bracket technique be used to over come the 29 cell reference
limit for any function?

thanks for this, much better than what i've been used to doing (the hidden additional column :))

On Thursday, October 15, 2009 12:57:12 AM UTC+8, Gord Dibben wrote:
=SUM((more,than,30,cells))

Note the double parens.


Gord Dibben MS Excel MVP

On Monday, October 12, 2009 2:02:01 PM UTC-7, jmcclain wrote:
I am trying to sum more than 30 cells (non sequential). I have used the
following for any # of cells less than 30

=sum(a1,a4,a7,a9)

However, how can I handle this when I have more than 30 cells to total?

The spreadsheet is 15 years old and would be almost impossible to modify.
Is there a different function that can accomplish this goal?

Thanks

Jon

Hi Jon,

You got plenty of suggestions but you could select all the cells you want to sum and name them. I selected 40 cells and named them Thurty then used =sum(Thurty)

HTH
Regards,
Howard