Summing non-contiguous cells

[Guest]

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

 

[Biff]

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

 

[Bob Phillips]

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)

 

[Guest]

Hi back!

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

 

[Guest]

Thanks Bob!

I'll give that a try.

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)

 

[Guest]

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

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

 

[Peo Sjoblom]

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,A60,A65,A70,A75,A80,A85,A90
,A95,A100,A105,A110,A115,A120,A125,A130,A135,A140,A145),A150,A155,A160,A165
and so on)