error in excell 2007

[Guest]

hi could any one help me with this error, i am adding up a lod of numbers but
get this error
More arguments have been specified for this function than are allowed in the
current file format.
is ther away to alter hao many function allowed in each cell ?
thank you for any help

mike7of_9

 

[Roger Govier]

Hi Mike

You will need to post the formula or entry you are making in order for
anyone to help you.

 

[Guest]

Hi Thanks Roge
=SUM(H23*M23,H24*M24,H25*M25,H26*M26,H28*M28,H29*M29,H30*M30,H31*M31,H32*M32,H34*M34,H35*M35,H36*M36,H38*M38,H39*M39,H41*M41,H42*M42,H43*M43,H45*M45,H46*M46,H47*M47,H48*M48,H49*M49,H51*M51,H52*M52,H54*M54,H55*M55,H56*M56,H57*M57,H58*M58,H59*M59)

hi as you can see im multipying h23*m23 both of these cells have numbers in
then using , to add to the next formula but i would like to add some more but
after h59*m59 i get the error posted earler

hope this might help

thanks

mike7of_9

 

[Dave Peterson]

Maybe you could use a few sumproducts:

=sumproduct(h23:h26,m23:m26)+sumproduct(h28:h32,m28:m32)+....

If there are numbers in those cells that you avoided, then don't do this. But
if there's text in those cells, maybe you could use:

=sumproduct(h23:h59,m23:m59)

 

[Sandy Mann]

If the other suggestions do not do what you want try enclosing the elements
in double brackets instead of single ones, it will let you enter more
elements than the 30 limit, at least it does in earlier versions.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Roger Govier]

Hi Mike

In XL2007, Sum will take up to 255 numbers, 1 number being represented
by each of your products.
You only have 31, so I do not understand why you are getting the
problem. I happily extended your range to 51 numbers without a problem
(but got bored with typing at this point<g>)

As Dave has suggested, you could use Sumproduct.
If there are numbers in the cells being omitted, it would still probably
be less typing to use
=SUMPRODUCTS(H3:H70,M23,M70)-H27*M27-H33*M33
dependent upon how far down the range you want to extend, and how many
rows you need to omit.

 

[T. Valko]

i would like to add some more but after h59*m59 i get the error posted

earler

get this error
More arguments have been specified for this function than are allowed in
the current file format.

Sounds like they may have opened a *.xls file in Excel 2007 and entered the
formula with more than 30 arguments.

Biff

 

[Roger Govier]

Good thinking, Biff!!!
That's exactly what the problem is.
Opening an xls file in compatibility mode, gives this error once you
pass 32 numbers.
A newly created xlsx file, allows the full 255 numbers.
Of course, you could not then save that file in 97-2003 compatibility
mode.

 

[Guest]

Thank you all, i converted my workbook in to the new file format and this
seems to have solved it all your sujestions helped.

mike

 

[euro bum]

I'm using your Fastfilter code. Could you show how to filter date column and what is the caret for OR condition. Thanks.