Is there a limit on non contiguous cells that can be summed in Excel?

[dw027]

We are attempting to sum several cells that are not contiguous. There appears to be a limit of 30 cells that can be added up. Is 30 cells the limit?

Has anyone seen this happen or are we just lucky?

 

[CautionMP]

30 in Excel 2k.

 

[keun]

hmmm. how about you "sub-sum" the totals. sum up to thirty in various cells and then sum those cells.

 

[SkipVought]

Hi,

I'd guess that you have a row/column of data where every 2nd, 3rd or 4th value is the one you want.

Make a helper row/column that you can later hide that is numbered, for instance

1,2,1,2,1,2,1,2,... for the first instance
1,2,3,1,2,3,1,2,3... for the second instance
1,2,3,4,1,2,3,4,1,2,3,4 for the last instance.

BTW the MOD function will do this numbering "automatically" using the ROW() or COLUMN() function and 2,3 or 4 as the second argument.

Then use SUMIF with the helper row/column

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[Gavona]

Further thoughts:

1. Have a look at =subtotal(9,range)e.g.
A5 =subtotal(9,A1:A4)
A10 =subtotal(9,A6:A9)
A12 =subtotal(9,A1:A11) This does not doublecount the subtotals
Beware that subtotals only act on visible cells - if you filter some rows out then.....

2.I haven't come across the 30 limit. You can certainly get more than that using the format = A1+A3+A5.....
Autosum works quite well. Highlight your list including the subtotals and click autosum - produces a formula = Sum(a1,a7,...) but I guess that is where the 30 limit comes in.



Gavin

 

[GlennUK]

If you use a defined name that consists of the non-contiguous range, then SUM will work on that, on well over 30 individual regions.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Is there a pattern to the cells - Give us the first few and then see if we can't just give you a formula to do it. Failing that, as has been suggested use =A1+A3+A7 etc, or even =SUM(A1,A3,A7...)+SUM(A45,A56,A66...)+SUM(...

A formula would be a better option though.

I'm assuming this isn't all random cells, so help us out with the logic behind what is being summed and lets see what we can do.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------