Bob Phillips wrote:
=SUMPRODUCT(--(MOD(ROW(A1:A1000,4)=1)),A1:A1000)
this adds row 1,5,9, etc. if you want 2,6,10 change =1 to =2, 3,7,11 change
=1 to =3, and finally, 4,8,12 change =1 to =0

Besides the obvious typo (misplaced right parenthesis for MOD), the
problem with solutions like this, which rely simply on the MOD of the
row number, is that if the range is moved (e.g. insert a row above it),
the formula no longer works unless the user remembers to change it.

In this case, I prefer the more obvious array formulation, even though
I usually prefer to avoid array formulas. But if you are enamored to
arcane SUMPRODUCT formulations (ever wonder why so many people ask what
"--" means?), I would opt for combining the two ideas as follows:

=sumproduct( --(mod(row(A1:A1000)-row(A1),4)=0), A1:A1000 )

Of course, replace A1:A1000 with the correct range.

Arcane, don't make me laugh.

Ever wondered why people ask why an array formula doesn't work?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Bob Phillips wrote:
=SUMPRODUCT(--(MOD(ROW(A1:A1000,4)=1)),A1:A1000)
this adds row 1,5,9, etc. if you want 2,6,10 change =1 to =2, 3,7,11
change
=1 to =3, and finally, 4,8,12 change =1 to =0

Besides the obvious typo (misplaced right parenthesis for MOD), the
problem with solutions like this, which rely simply on the MOD of the
row number, is that if the range is moved (e.g. insert a row above it),
the formula no longer works unless the user remembers to change it.

In this case, I prefer the more obvious array formulation, even though
I usually prefer to avoid array formulas. But if you are enamored to
arcane SUMPRODUCT formulations (ever wonder why so many people ask what
"--" means?), I would opt for combining the two ideas as follows:

=sumproduct( --(mod(row(A1:A1000)-row(A1),4)=0), A1:A1000 )

Of course, replace A1:A1000 with the correct range.

Bob Phillips wrote:
Arcane, don't make me laugh.
Ever wondered why people ask why an array formula doesn't work?

Touche'. I expected that response, exactly as you said it ;-). I
don't think either one of us can prove which is more confusing and
which invokes more questions. It's a moot point.

Well I am glad to see a smile after the tone of the previous post.

Personally, I don't think it is a moot point, or that one is more confusing
than the other. Neither is arcane, they both have vagaries, idiosyncrasies,
call it what you may, that you need to understand if you want to use them
effectively. If you (you being one, the OP) just wants a solution (which too
many do unfortunately), then the SUMPRODUCT solution is better as it will
work out of the box, not need to explain anything such as CSE. I am not
saying that is an argument in favour of SP, just a factor.

Myself, I think array formulae are great, I think that SP formulae are
great. Sometimes better to use one, sometimes the other.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Bob Phillips wrote:
Arcane, don't make me laugh.
Ever wondered why people ask why an array formula doesn't work?

Touche'. I expected that response, exactly as you said it ;-). I
don't think either one of us can prove which is more confusing and
which invokes more questions. It's a moot point.

Another small thing to add is that once you understand how -- works, it can
be used elsewhere, such as testing a date so --"2006-07-12", which makes
more readable formulae IMO, which is very important.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Bob Phillips wrote:
Arcane, don't make me laugh.
Ever wondered why people ask why an array formula doesn't work?

Touche'. I expected that response, exactly as you said it ;-). I
don't think either one of us can prove which is more confusing and
which invokes more questions. It's a moot point.