My first post - Please Help (Formula for adding Values)

[leapyleigh]

Hello !!

HELP!!

I have a report I need to finish by the end of the week and I am
dealing with hundreds of rows.

I need to be able to add up the total of every 4th row only.

I cannot for the life of me figure out what forumla I need to use.

Any help would be greatly appreciated!!

Thanks,
LeapyLeigh

 

[David Biddulph]

Hello !!

HELP!!

I have a report I need to finish by the end of the week and I am
dealing with hundreds of rows.

I need to be able to add up the total of every 4th row only.

I cannot for the life of me figure out what forumla I need to use.

Any help would be greatly appreciated!!

One way is to use a formula like =IF(MOD(ROW(),4)=1,A1,"") and total that
column.

There will doubtless be clever things with SUMIF and SUMPRODUCT that other
contributors will be able to suggest.

 

[joeu2004]

I need to be able to add up the total of every 4th row only.

Do we have the same understanding of what "every 4th row" means? To
me, it means A1, A5, A9, etc for example. The following array formula
will do that (every 4th row starting A1 and ending with the 4th row at
or before A17):

=SUM( IF(MOD(ROW(A1:A17)-ROW(A1),4)=0, A1:A17) )

Type ctrl-shift-Enter, not Enter, after typing or editing the formula.

 

[joeu2004]

PS.....

Do we have the same understanding of what "every 4th row" means? To
me, it means A1, A5, A9, etc for example. The following array formula
will do that (every 4th row starting A1 and ending with the 4th row at
or before A17):

=SUM( IF(MOD(ROW(A1:A17)-ROW(A1),4)=0, A1:A17) )

Type ctrl-shift-Enter, not Enter, after typing or editing the formula.

I should note that that array formula works regardless of where the
range begins. For example, if you wanted every 4th row starting in A2,
simply replace all occurrences of A1 with A2.

Also, when I wrote "ending with the 4th row at or before A17", I meant:
counting from the first row of the range, not from the end of the
range.

 

[Bob Phillips]

=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

--
HTH

Bob Phillips

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

 

[joeu2004]

=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]

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)

 

[joeu2004]

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.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[leapyleigh]

Do we have the same understanding of what "every 4th row" means? To
me, it means A1, A5, A9, etc for example. The following array formula
will do that (every 4th row starting A1 and ending with the 4th row at
or before A17):

=SUM( IF(MOD(ROW(A1:A17)-ROW(A1),4)=0, A1:A17) )

Type ctrl-shift-Enter, not Enter, after typing or editing the formula.

This is the formula I used.... thank you sooo much mate!!

Sigh...I wish work would spring for some bloody training so that I can
perform the miracles that they want from me!!

I have had no "official" training on excel, everything I know has been
figured out on the job, and from picking other peoples brains!!

Thanks so much again!!

 

[Gord Dibben]

Welcome to the club<g>

I would bet very few have any formal Excel training.

Trial and error and brain-picking is the norm.


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Waht's training?

Welcome to the club<g>

I would bet very few have any formal Excel training.

Trial and error and brain-picking is the norm.


Gord Dibben MS Excel MVP

 

[Bob Phillips]

indide, wots speeling?

--
HTH

Bob Phillips

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

 

[Gord Dibben]

Geez Bob.

You shood yews your speel chicken befour pasting to theez groops

indide, wots speeling?

Gord Dibben MS Excel MVP