How to use SUMIF function with non-adjacent cells
 

I have used the SUMIF function many times on worksheets that have the same
type of information in a "range". Now I need to use the SUMIF function in a
spreadsheet where the "ranges" are not adjacent to each other. For example
in range J1 to J289 I only need every third numbered row for the SUMIF
function. I have Named the range using every third row, but when I try to
insert the Named range into the SUMIF formula, I always get an error.
My range name looks like this j1,j4,j7,j11 etc. These are rows I want to
SUMIF.

How to use SUMIF function with non-adjacent cells
 

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)

--
HTH

Bob Phillips

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

"KLaw" wrote in message
...
I have used the SUMIF function many times on worksheets that have the same
type of information in a "range". Now I need to use the SUMIF function in
a
spreadsheet where the "ranges" are not adjacent to each other. For
example
in range J1 to J289 I only need every third numbered row for the SUMIF
function. I have Named the range using every third row, but when I try to
insert the Named range into the SUMIF formula, I always get an error.
My range name looks like this j1,j4,j7,j11 etc. These are rows I want to
SUMIF.

How to use SUMIF function with non-adjacent cells
 

On Wed, 18 Oct 2006 22:58:33 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)

Probably the lateness of the hour and a typo.
I guess Bob means Row, not Column in the above :-)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to use SUMIF function with non-adjacent cells
 

Time to knock-out the z's Richard.

Bob

"Richard Buttrey" wrote in
message ...
On Wed, 18 Oct 2006 22:58:33 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)

Probably the lateness of the hour and a typo.
I guess Bob means Row, not Column in the above :-)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to use SUMIF function with non-adjacent cells
 

Trying to match me, Bob ? <g

Pete

Bob Phillips wrote:
Time to knock-out the z's Richard.

Bob

"Richard Buttrey" wrote in
message ...
On Wed, 18 Oct 2006 22:58:33 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)

Probably the lateness of the hour and a typo.
I guess Bob means Row, not Column in the above :-)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

How to use SUMIF function with non-adjacent cells
 

No, I went to bed just as you got started <bg

Bob

"Pete_UK" wrote in message
oups.com...
Trying to match me, Bob ? <g

Pete

Bob Phillips wrote:
Time to knock-out the z's Richard.

Bob

"Richard Buttrey" wrote in
message ...
On Wed, 18 Oct 2006 22:58:33 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)

Probably the lateness of the hour and a typo.
I guess Bob means Row, not Column in the above :-)

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________