# SumIf with discontinuous cells?

#### rbeeman

##### Board Regular
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)

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Oaktree

##### MrExcel MVP
Why not use three SUMIFs and add them together?

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

#### rbeeman

##### Board Regular
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

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

#### vane0326

##### Well-known Member

Maybe...

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

Hope it helps!

#### rbeeman

##### Board Regular
I'll try that....thank you both!

what do the "--" do?

Replies
2
Views
151
Replies
3
Views
123
Replies
11
Views
172
Replies
7
Views
268
Replies
10
Views
230