Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

Hi Denise

If you want to start on row 17 and use values for every 13th row, then

=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4))
--
Regards

Roger Govier


"denise" wrote in message
...
Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

Surely your formula should be:

=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4))

It then works for me. Do you have #VALUE! in your data anywhere?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify
a
formula.

This formula is working now (in J3) to sum starting with the value in row
11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

There aren't any visible #VALUE! errors in the range however when I evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to modify a
formula.

This formula is working now (in J3) to sum starting with the value in row 11
and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17 and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I can't
figure out what it should be. Any ideas?

Thanks,
Denise

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that should
be numbers. I set the format to number and still get False. These number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day 1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row 17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise

Denise,

Re-formatting will not change what is actually being held in the cell.
Having said that I am about to tell you to do just that!

Try entering a new formula:

=--('1'!T22)

That will change the cell contents into a number so try

=ISNUMBER(A56) should be TRUE

and =ISTEXT(A65) should return FALSE

When I tested it in XL97 it automatically re-formatted the cell to Text so I
re-formatted it back to General just to be consistent'

Then evaluate the formula againa and see it you are still getting the
#VALUE!


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
Hi Sandy,

Thanks for your suggestion. I do get False for some of the cells that
should
be numbers. I set the format to number and still get False. These number
cells are actually formulas to pull the number results from a different
sheet: ='1'!T22

Denise

"Sandy Mann" wrote:

Try working out which cell one of the #VALUE! returns is being returned
from - say it was A65 - then try in an empty cell the formula:

=ISNUMBER(A56)

If you do not get TRUE then the entry is text even if it looks like a
number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"denise" wrote in message
...
There aren't any visible #VALUE! errors in the range however when I
evaluate
the formula, I see #VALUE! in most of the cells. The way the
spreadsheet
is
set up every other line is text:

row 16 Concession
row 17 1
row 18 Affidavit
row 19 0
row 20 AWAS
row 21 0
etc., with the items repeating every 13th row (rows 15-27 are for Day
1,
rows 28-40 for Day 2...)

Could the text be causing the #VALUE! error?

Thanks,
Denise

"JE McGimpsey" wrote:

It works fine for me. Do you have any #VALUE! errors in O17:O407??

However, if you want to start with the value in row 17, use

(MOD(ROW(17:407),13)=4)

instead.

In article ,
denise wrote:

Hello folks,

I'm hoping someone can help me since I have hit a snag in trying to
modify a
formula.

This formula is working now (in J3) to sum starting with the value
in
row 11
and then adding every 7th row:
=SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4))

I want to adapt this (in O4) to sum starting with the value in row
17
and
then add every 13th row. I've tried
=SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE.
I'm
guessing the problem is with the =3 at the end of the formula but I
can't
figure out what it should be. Any ideas?

Thanks,
Denise