SUMIF non-contiguous range

L

Lady_Olara

range:
C3,C14,C25,C34,C41

criteria:
0
Click to expand...

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.
 
G

goober

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(A1>0,SUM(D1,D3,D5,D7,D9,D11,D13,D15,D17),"False")

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.
 
D

Domenic

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!
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Ragdyer

I like that Bob!

Even works without the "Product":

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

Lady_Olara

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.
 
D

Domenic

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!
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
D

Domenic

Bob Phillips said:
...although his does have the advantage of being
more easily copyable to other cells...
Click to expand...

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

Bob Phillips

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

still like it better though <vbg>

Bob
 
D

Domenic

Bob Phillips said:
Good point, although that rarely becomes an issue, whereas maintainability
always is, and copying very often is.
Agreed...

still like it better though <vbg>
Click to expand...

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

RagDyer

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>
 
Top