Summing non-contiguous cells
 

I want to sum 52 non-contiguous cells in column E. The SUM function limits
me to 30. Is there a way around this?

Hi!

Is there a pattern such as sum every other cell or sum every 5th cell?

Or, is there a common corresponding value in another column that can be used
as a "key"?

Biff

"Cheryl" wrote in message
...
I want to sum 52 non-contiguous cells in column E. The SUM function limits
me to 30. Is there a way around this?

Bracket some of them, e.g.

=SUM((A1,a3,a5,a7),(B1,B3,B5,B7),...)

you get the idea

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cheryl" wrote in message
...
I want to sum 52 non-contiguous cells in column E. The SUM function
limits
me to 30. Is there a way around this?

Hi back!

Yes, I want to sum every 19th cell in column E.

"Biff" wrote:

Hi!

Is there a pattern such as sum every other cell or sum every 5th cell?

Or, is there a common corresponding value in another column that can be used
as a "key"?

Biff

"Cheryl" wrote in message
...
I want to sum 52 non-contiguous cells in column E. The SUM function limits
me to 30. Is there a way around this?

Thanks Bob!

I'll give that a try.

"Bob Phillips" wrote:

Bracket some of them, e.g.

=SUM((A1,a3,a5,a7),(B1,B3,B5,B7),...)

you get the idea

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cheryl" wrote in message
...
I want to sum 52 non-contiguous cells in column E. The SUM function
limits
me to 30. Is there a way around this?

You don't need the sum formula - just add 'em up

=A2+A5+A6+A8+A9+A22+A24+A27 etc

"Cheryl" wrote:

I want to sum 52 non-contiguous cells in column E. The SUM function limits
me to 30. Is there a way around this?

If you want to sum cell 1, then every 19th row
(E1, E20 E39 etc)

=SUMPRODUCT(--(MOD(ROW(E1:E5000),19)=1),E1:E5000)

if you want to sum cell 19, 38 and so on

=SUMPRODUCT(--(MOD(ROW(E1:E50),19)=0),E1:E50)

also if you need more than 30 just wrap it in an extra parenthesis



=SUM((A5,A10,A15,A20,A25,A30,A35,A40,A45,A50,A55,A 60,A65,A70,A75,A80,A85,A90
,A95,A100,A105,A110,A115,A120,A125,A130,A135,A140, A145),A150,A155,A160,A165
and so on)



--
Regards,

Peo Sjoblom


"Cheryl" wrote in message
...
Hi back!

Yes, I want to sum every 19th cell in column E.

"Biff" wrote:

Hi!

Is there a pattern such as sum every other cell or sum every 5th cell?

Or, is there a common corresponding value in another column that can be
used
as a "key"?

Biff

"Cheryl" wrote in message
...
I want to sum 52 non-contiguous cells in column E. The SUM function
limits
me to 30. Is there a way around this?