How to use SUMIF function with non-adjacent cells

G

Guest

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.
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Richard Buttrey

=SUMPRODUCT(--(MOD(COLUMN(J1:J289),3)=1),J1:J289)
Click to expand...

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
__________________________
 
B

Bob Phillips

Time to knock-out the z's Richard.

Bob

Richard Buttrey said:
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
__________________________
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif of non-adjacent cells 1
SUMIF() to add cells in non-contiguous ranges? (Excel 2003) 4
SUMIF with non-adjancent cells 3
SUMIF on a Calculated Field 3
SUMIF Function 2
sumif function 3
Specifying comparision criteria with SUMIF() 2
SUMIF formula with non contiguous cells 2

Top