SumIf with discontinuous cells?

rbeeman

Board Regular
Joined
Jun 10, 2005
Messages
66
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)
 

Some videos you may like

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
Joined
Jun 20, 2002
Messages
7,984
Why not use three SUMIFs and add them together?

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

rbeeman

Board Regular
Joined
Jun 10, 2005
Messages
66
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
Joined
Jun 20, 2002
Messages
7,984
=SUMPRODUCT(--(MOD(ROW($A$1:$A$20),5)=0),--($A$1:$A$20>0),$B$1:$B$20)
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819

ADVERTISEMENT

Maybe...


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



Hope it helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top