How to sum more than 30 cells

[jmcclain]

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

 

[Don Guillett]

=sum(a1,a4)+sum(a7,a9)

 

[Dave Peterson]

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]

Thanks greatly -

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

 

[jmcclain]

Thanks greatly...

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

 

[Gord Dibben]

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

Note the double parens.


Gord Dibben MS Excel MVP

 

[Ashish Mathur]

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]

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

 

[jacqmagno]

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

 

[lhkittle]

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