SUMIF non-contiguous range
 

range:
C3,C14,C25,C34,C41

criteria:
0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the
function off.

SUMIF non-contiguous range
 

Here is a possible solution to your problem. For example if A1 is
greater than 0 then this formula would sum all the cells listed. If A1
is 0 or less then the result if "False".

=IF(A10,SUM(D1,D3,D5,D7,D9,D11,D13,D15,D17),"Fals e")

Hope it helps.

BTW. there is a limit to the number of cells you can use this way but I
can't remember how many it is.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=499168

SUMIF non-contiguous range
 

One way...

=SUMPRODUCT(--(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0),CHOOSE( {1,2,3,4,
5},C3,C14,C25,C34,C41))

Hope this helps!

In article ,
"Lady_Olara" wrote:

range:
C3,C14,C25,C34,C41

criteria:
0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the
function off.

SUMIF non-contiguous range
 

=SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" wrote in message
...
range:
C3,C14,C25,C34,C41

criteria:
0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the
function off.

SUMIF non-contiguous range
 

I like that Bob!

Even works without the "Product":

=SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0"))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
=SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" wrote in message
...
range:
C3,C14,C25,C34,C41

criteria:
0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the
function off.

SUMIF non-contiguous range
 

Would you all be so kind as to explain your solutions? I have not even heard
of some of these functions before, and the help pages aren't written for
someone who is really rusty on the technical side of Excel.

Thanks in advance.

SUMIF non-contiguous range
 

Let's assume that C3, C14, C25, C34, and C41 contain the following
values...

100
0
150
125
-250

....and we have the following formula...

=SUMPRODUCT(--(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0),CHOOSE( {1,2,3,4,
5},C3,C14,C25,C34,C41))

....here's how it breaks down...

CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0 evaluates to:

TRUE
FALSE
TRUE
TRUE
FALSE

--(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0) evaluates to:

1
0
1
1
0

Notice that the double negative coerces TRUE and FALSE to their
numerical equivalent of 1 and 0, respectively.

CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41) evaluates to:

100
0
150
125
-250

SUMPRODUCT multiplies these two arrays...

=SUMPRODUCT({1,0,1,1,0},{100,0,150,125,-250})

....which gives us...

=SUMPRODUCT({100,0,150,125,0})

....and which it sums and returns 375.

Hope this helps!

In article ,
"Lady_Olara" wrote:

Would you all be so kind as to explain your solutions? I have not even heard
of some of these functions before, and the help pages aren't written for
someone who is really rusty on the technical side of Excel.

Thanks in advance.

SUMIF non-contiguous range
 

Thanks RD.

Indeed it does work with just SUM, which makes it better IMO (must get help
for this SP fetish I have <vbg).

I like it better than Domenic's solution as it is much easier to add to
(with Domenic's you have to add another entry to the array constants every
time you add another cell), although his does have the advantage of being
more easily copyable to other cells. Swings and roundabouts as always.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ragdyer" wrote in message
...
I like that Bob!

Even works without the "Product":

=SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0"))
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Bob Phillips" wrote in message
...
=SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" wrote in message
...
range:
C3,C14,C25,C34,C41

criteria:
0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing
the
function off.

SUMIF non-contiguous range
 

In my solution, INDIRECT is used to get around the fact that SUMIF doesn't
accept a non-contiguous range by creating an array of values from those
non-contiguous cells. Using Domenic's example values, it passes an array
{100,0,150,125,-250} to SUMIF, which is test for greater than 0, "0", and
this in turn passes an array {100,0,150,125,0}to the SUMPRODUCT function (or
even SUM as RD points out). Note that the original -250 is transformed to 0
as it fails the 0 test.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" wrote in message
...
Would you all be so kind as to explain your solutions? I have not even
heard
of some of these functions before, and the help pages aren't written for
someone who is really rusty on the technical side of Excel.

Thanks in advance.

SUMIF non-contiguous range
 

In article ,
"Bob Phillips" wrote:

...although his does have the advantage of being
more easily copyable to other cells...

....and it doesn't include a volatile function, such as INDIRECT... :)

SUMIF non-contiguous range
 

Good point, although that rarely becomes an issue, whereas maintainability
always is, and copying very often is.

still like it better though <vbg

Bob

"Domenic" wrote in message
...
In article ,
"Bob Phillips" wrote:

...although his does have the advantage of being
more easily copyable to other cells...

...and it doesn't include a volatile function, such as INDIRECT... :)

SUMIF non-contiguous range
 

In article ,
"Bob Phillips" wrote:

Good point, although that rarely becomes an issue, whereas maintainability
always is, and copying very often is.

Agreed...

still like it better though <vbg

Yep, I personally have no problem with it... :)

SUMIF non-contiguous range
 

Ragdyer wrote:
I like that Bob!

Even works without the "Product":

=SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0"))

See also a previous occasion on the matter:

http://www.mrexcel.com/board2/viewtopic.php?t=29977

SUMIF non-contiguous range
 

Thanks for the link ... very interesting !

Goes to show that very little is really original around here.

However, I'm sure that the second caveman who figured out about bringing a
burning brand into the cave to produce light, felt no less a sense of
accomplishment then the first one, since both were independently contrived
.... NOT that I'm calling Bob a caveman!<vbg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Aladin Akyurek" wrote in message
...


Ragdyer wrote:
I like that Bob!

Even works without the "Product":

=SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0"))

See also a previous occasion on the matter:

http://www.mrexcel.com/board2/viewtopic.php?t=29977