SumIf with discontinuous cells?

[rbeeman]

I need to use the SumIF (and SumProduct) on a collection of summary row cells that have sub-component data between them and are therefore discontinuous. A comma is used to separate these values in the formula, but a comma is also used to separate the three components of the SumIf formula syntax....so it is not working. I tried putting extra parens around the range data, but still doesn't work. Any ideas?

PS...added complicating factor...I can't use a 'name the ranges' approach here (long story)

 

[Oaktree]

Why not use three SUMIFs and add them together?

=SUMIF(range1,criterion,sumrange1)+SUMIF(range2,criterion,sumrange2)+SUMIF(range3,criterion,sumrange3)

 

[rbeeman]

I'm not exactly sure how that would work? What I'm trying to do is something like...

SumIf(a5,a10,a15,a20,">0",b5,b10,b15,b20)

....and this is just the denominator in a larger formula that uses same approach with SumProduct in the numerator.

 

[Oaktree]

=SUMPRODUCT(--(MOD(ROW($A$1:$A$20),5)=0),--($A$1:$A$20>0),$B$1:$B$20)

 

[vane0326]

Maybe...


=SUMPRODUCT((MOD(ROW(A5:A20),5)=0)*(A5:A20>0),B5:B20)



Hope it helps!

 

[rbeeman]

I'll try that....thank you both!

what do the "--" do?

 

[Oaktree]

what do the "--" do?

In fewer than 10 words, they change TRUE/FALSE to 1/0.

For a more detailed description, see http://www.mrexcel.com/board2/viewtopic.php?t=179748