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

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
:) :) :)


--
leapyleigh
------------------------------------------------------------------------
leapyleigh's Profile: http://www.excelforum.com/member.php...o&userid=36275
View this thread: http://www.excelforum.com/showthread...hreadid=560565

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

"leapyleigh" wrote
in message ...

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.
--
David Biddulph

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

leapyleigh wrote:
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.

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

PS.....

I wrote:
leapyleigh wrote:
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.

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.

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

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

"leapyleigh" wrote
in message ...

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
:) :) :)


--
leapyleigh
------------------------------------------------------------------------
leapyleigh's Profile:
http://www.excelforum.com/member.php...o&userid=36275
View this thread: http://www.excelforum.com/showthread...hreadid=560565

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

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.

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

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.

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

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.

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

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.

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

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.

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

Wrote:
leapyleigh wrote:
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.


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!!:)


--
leapyleigh
------------------------------------------------------------------------
leapyleigh's Profile: http://www.excelforum.com/member.php...o&userid=36275
View this thread: http://www.excelforum.com/showthread...hreadid=560565

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

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

On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh
wrote:

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

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

Waht's training?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh
wrote:

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

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

indide, wots speeling?

--
HTH

Bob Phillips

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

"Bob Phillips" wrote in message
...
Waht's training?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh
wrote:

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

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

Geez Bob.

You shood yews your speel chicken befour pasting to theez groops



On Thu, 13 Jul 2006 11:56:23 +0100, "Bob Phillips"
wrote:

indide, wots speeling?

Gord Dibben MS Excel MVP