SumIf with discontinuous cells?
 

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?

SumIf with discontinuous cells?
 

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?

SumIf with discontinuous cells?
 

I tried something like...

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

and...

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

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

"Dave F" wrote:

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?

SumIf with discontinuous cells?
 

Here's something to try:

=IF(AND(A50,A100,A150,A200),SUM(A5,A10,A15,A20 ,B5,B10,B15,B20),"")

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

I tried something like...

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

and...

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

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

"Dave F" wrote:

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?

SumIf with discontinuous cells?
 

RBee,

=SUMPRODUCT((MOD(ROW(A1:A100),5)=0)*(A1:A1000)*B1 :B100)

HTH,
Bernie
MS Excel MVP


"RBee" wrote in message
...
I tried something like...

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

and...

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

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

"Dave F" wrote:

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?

SumIf with discontinuous cells?
 

thanks!....two questions:
-wouldn't that add all cells in A & B if condition is met?
-more importantly, that would add ALL values when ALL rows are 0, what I
want to do is to add ONLY those rows in B when the corresponding row in A is
o.

"Dave F" wrote:

Here's something to try:

=IF(AND(A50,A100,A150,A200),SUM(A5,A10,A15,A20 ,B5,B10,B15,B20),"")

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

I tried something like...

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

and...

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

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

"Dave F" wrote:

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?

SumIf with discontinuous cells?
 

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

"Bernie Deitrick" wrote:

RBee,

=SUMPRODUCT((MOD(ROW(A1:A100),5)=0)*(A1:A1000)*B1 :B100)

HTH,
Bernie
MS Excel MVP


"RBee" wrote in message
...
I tried something like...

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

and...

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

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

"Dave F" wrote:

Show us an example of a formula you tried.

Dave
--
Brevity is the soul of wit.


"RBee" wrote:

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?