MOD and ROW

Guest · Jan 5, 2004

When using this formula to add every 4th value in a column...

=SUMPRODUCT((B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1))

If there are any cells in B1:B31 that are text, that are not to be added,I get #value. Can can I fix this.
Thanks in advance
Steve

Norman Harker · Jan 5, 2004

Hi steveh!

Try this:

=SUMPRODUCT(ISNONTEXT(B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

steveh said:
When using this formula to add every 4th value in a column...

=SUMPRODUCT((B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1))

If there are any cells in B1:B31 that are text, that are not to be

added,I get #value. Can can I fix this.

Guest · Jan 5, 2004

Well....I got a number back, but it is less than any one of the cells in the range

Harlan Grove · Jan 5, 2004

Norman Harker said:
Try this:

=SUMPRODUCT(ISNONTEXT(B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1)) ....

OP's formula is a conditional *SUM*, Norman's formula is a conditional
*COUNT*. [Digression: why ISNONTEXT rather than ISNUMBER?]

If the OP is summing a range that could include text values, then SUMPRODUCT
won't work, and an array SUM(IF(...)) is what's needed.

=SUM(IF(ISNUMBER(-B1:B31),B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)=0))

Norman Harker · Jan 5, 2004

Thanks for correction Harlan.

Inadequate test data used!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Guest · Jan 5, 2004

Thanks Harlan and Norman
I can get it to work in the form as Harlan posted but when modifying my original formula I get too many argument error....
My Formula....
=IF(OR($F$2=7,$E$2="holiday"),SUMPRODUCT((C24:C116)*(MOD(ROW(C24:C116)-ROW(C24),4)+1=1)),SUMPRODUCT((C24:C48)*(MOD(ROW(C24:C48)-ROW(C24),4)+1=1))+C116)

Recovering from bronchitis Im sure Im missing something.

Thanks again!

Dana DeLouis · Jan 5, 2004

If I am not mistaken, with SUMPRODUCT's array version, one just needs to
separate the possible text values via a comma.

=SUMPRODUCT(rng,0+(MOD(ROW(rng),4)=1))

or perhaps the more popular ...

=SUMPRODUCT(rng,--(MOD(ROW(rng),4)=1))

rng would be B1:B31
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Harlan Grove said:
Norman Harker said:

Try this:

=SUMPRODUCT(ISNONTEXT(B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1)) ...

Click to expand...

OP's formula is a conditional *SUM*, Norman's formula is a conditional
*COUNT*. [Digression: why ISNONTEXT rather than ISNUMBER?]

If the OP is summing a range that could include text values, then SUMPRODUCT
won't work, and an array SUM(IF(...)) is what's needed.

=SUM(IF(ISNUMBER(-B1:B31),B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)=0))

Harlan Grove · Jan 5, 2004

steveh said:
I can get it to work in the form as Harlan posted but when modifying
my original formula I get too many argument error....
My Formula....
[reformatted]
=IF(OR($F$2=7,$E$2="holiday"),
SUMPRODUCT((C24:C116)*(MOD(ROW(C24:C116)-ROW(C24),4)+1=1)),
SUMPRODUCT((C24:C48)*(MOD(ROW(C24:C48)-ROW(C24),4)+1=1))+C116)

....

Using this *EXACT* formula, I don't get any errors. It looks OK to me.
Where, precisely, does Excel say that extraneous argument is?

Guest · Jan 5, 2004

This is the formula that I am trying to modify with your suggestions. It works unless there is text within the range. Im not quite sure how to make it work.

Harlan Grove · Jan 5, 2004

steveh said:
This is the formula that I am trying to modify with your suggestions.
It works unless there is text within the range. Im not quite sure
how to make it work.

Missed that. Try the array formula

=IF(OR($F$2=7,$E$2="holiday"),
SUM(IF(ISNUMBER(-C24:C116),C24:C116)*(MOD(ROW(C24:C116)-ROW(C24),4)=0)),
SUM(IF(ISNUMBER(-C24:C48,C24:C48)*(MOD(ROW(C24:C48)-ROW(C24),4)=0))+C116)

But Dana DeLouis's response is a better way to go (unless you also want to
filter out error values, which the formula above does as well).

Norman Harker · Jan 5, 2004

Hi steveh

It works OK for me.

Are you entering as an array?

=SUM(IF(ISNUMBER(-B1:B31),B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)=0))
Entered by pressing and holding down Ctrl + Shift and then pressing
Enter.

Appears in the formula bar as:
{=SUM(IF(ISNUMBER(-B1:B31),B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)=0))}

Checked with text in the range and including text as an entry in one
of the "4" rows.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

steveh said:
This is the formula that I am trying to modify with your

suggestions. It works unless there is text within the range. Im not
quite sure how to make it work.

Harlan Grove · Jan 5, 2004

Harlan Grove said:
=IF(OR($F$2=7,$E$2="holiday"),
SUM(IF(ISNUMBER(-C24:C116),C24:C116)*(MOD(ROW(C24:C116)-ROW(C24),4)=0)),
SUM(IF(ISNUMBER(-C24:C48,C24:C48)*(MOD(ROW(C24:C48)-ROW(C24),4)=0))+C116)

....

Typo fix. Make that

=IF(OR($F$2=7,$E$2="holiday"),
SUM(IF(ISNUMBER(-C24:C116),C24:C116)*(MOD(ROW(C24:C116)-ROW(C24),4)=0)),
SUM(IF(ISNUMBER(-C24:C48),C24:C48)*(MOD(ROW(C24:C48)-ROW(C24),4)=0))+C116)

Guest · Jan 5, 2004

Got it to work. Thanks for every one's help!!!

Jack Sons · Jan 6, 2004

Dana,

I tried with this range:

10
20
40
80
skateboard
auto
100
schaatsen
200
400
800
fiets
henk
1600
3300

The array entered formula (your first one) seems to add the numbers in bold
type (result is 4240). I cannot relate that to steveh's wish: "using this
formula to add every 4th value in a column". I see no connection between
"4th" and the bold numbers. 40 is not the 4th cell and also not the 4th
number (it is the 3rd number), after that 100 is either the 2nd number or
the 4th cell etc. How does this work? Please enlighten me.

Jack Sons
The Netherlands

Dana DeLouis said:
If I am not mistaken, with SUMPRODUCT's array version, one just needs to
separate the possible text values via a comma.

=SUMPRODUCT(rng,0+(MOD(ROW(rng),4)=1))

or perhaps the more popular ...

=SUMPRODUCT(rng,--(MOD(ROW(rng),4)=1))

rng would be B1:B31
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Harlan Grove said:

Norman Harker said:

Try this:

=SUMPRODUCT(ISNONTEXT(B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1)) ...
When using this formula to add every 4th value in a column...

=SUMPRODUCT((B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)+1=1))

If there are any cells in B1:B31 that are text, that are not to be
added,I get #value. Can can I fix this.

Click to expand...

OP's formula is a conditional *SUM*, Norman's formula is a conditional
*COUNT*. [Digression: why ISNONTEXT rather than ISNUMBER?]

If the OP is summing a range that could include text values, then SUMPRODUCT
won't work, and an array SUM(IF(...)) is what's needed.

=SUM(IF(ISNUMBER(-B1:B31),B1:B31)*(MOD(ROW(B1:B31)-ROW(B1),4)=0))

Click to expand...

Harlan Grove · Jan 6, 2004

I tried with this range: ...
The array entered formula (your first one) seems to add the numbers in bold
type (result is 4240). . . .

For those of us who see only plain text (by choice!), it's impossible to tell
which of your figures are in boldface. It appears you were totaling the 3rd,
7th, 11th and 15th entries. That wasn't quite what the OP seemed to be after.

. . . I cannot relate that to steveh's wish: "using this
formula to add every 4th value in a column". . . .

Depends on where you start. If you start at the topmost value, the result would
be 210, 2nd from top 2020, 3rd from top (what you did) 4240, and 4th from top
80. What was the top entry, 10, in your sample data in row 3 in your worksheet?

. . . I see no connection between
"4th" and the bold numbers. 40 is not the 4th cell and also not the 4th
number (it is the 3rd number), after that 100 is either the 2nd number or
the 4th cell etc. How does this work? Please enlighten me.

...

Dana winged it with regard to the precise MOD criteria. He should have stuck
with the OP's intent if not the OP's exact formulation. So

=SUMPRODUCT(Rng,--(MOD(ROW(Rng)-CELL("Row",Rng),4)=0))

This would pull every fourth row *STARTING* with the first row, so 1st, 5th,
9th, 13th, 17th, etc. That's controlled by the 0 in the formula above. If you
want to start with the 4th row, so 4th, 8th, 12th, etc., change 0 to 3.

Jack Sons · Jan 6, 2004

Harlan,

Did not know that "bold" would disappear in the mailprocess (I just copied
the range, with the bold added numbers, from my Excel sheet). I chose my
numbers so that one way only leads to any result. In this case 40, 100, 800,
3300 were added, those were the bold ones. Had no idea that the topmost cell
would not be cell 1.

Learned a lot, understood now that -- is a way to transform true and false
into numbers 1 or 0. When I used 1* I got the same result, of course.

Is -- generally preferred over 1* ?

Why does 1* and -- work but + or even ++ not?

TIA

Jack.

MOD and ROW

Guest

Norman Harker

Guest

Harlan Grove

Norman Harker

Guest

Dana DeLouis

Harlan Grove

Guest

Harlan Grove

Norman Harker

Harlan Grove

Guest

Jack Sons

Harlan Grove

Jack Sons