SUMIF with non-adjancent cells
 

Hi. Anyone knows how to use ranges of non-adjacent cells in a SUMIF function?

SUMIF with non-adjancent cells
 

Could you perhaps mean

=SUMIF(C10:C19,1,G20:G29)

Mike

"ismae" wrote:

Hi. Anyone knows how to use ranges of non-adjacent cells in a SUMIF function?

SUMIF with non-adjancent cells
 

No, I mean when the "range" and "sum_range" contain non-adjacent cells. For
example, I want to sum from cells C5,G5,K5,O5,.....,AI5 only the ones with
positive values. I tried the formula

=SUMIF((C5,G5,K5,O5,S5,W5,AA5,AE5,AI5),"0",(C5,G5 ,K5,O5,S5,W5,AA5,AE5,AI5))

but it returns #VALUE!





"Mike H" wrote:

Could you perhaps mean

=SUMIF(C10:C19,1,G20:G29)

Mike

"ismae" wrote:

Hi. Anyone knows how to use ranges of non-adjacent cells in a SUMIF function?

SUMIF with non-adjancent cells
 

I want to sum from cells C5,G5,K5,O5,.....,AI5
only the ones with positive values.

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C5:AI5)+1,4)=0),--(C5:AI50),C5:AI5)

--
Biff
Microsoft Excel MVP


"ismae" wrote in message
...
Hi. Anyone knows how to use ranges of non-adjacent cells in a SUMIF
function?