SUMIF non-contiguous range

[Lady_Olara]

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.

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

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

In article <[email protected]>,
"Lady_Olara" <[email protected]> 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.

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"}),">0"))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Lady_Olara" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

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

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

In article <[email protected]>,
"Lady_Olara" <[email protected]> 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.

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

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"}),">0"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Lady_Olara" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
>

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

"Lady_Olara" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Domenic]

In article <#[email protected]>,
"Bob Phillips" <[email protected]> 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...

[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

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> In article <#[email protected]>,
> "Bob Phillips" <[email protected]> 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...

[Domenic]

In article <#[email protected]>,
"Bob Phillips" <[email protected]> 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...

[Aladin Akyurek]

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

[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>
--
Regards,

RD

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


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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

[cdvipul]

U r awesum Domenic

[asrivera]

hi all.. i found this thread and it solves what i need to do.
however, the formula does not work when i copy down.
the formula is in yellow cells. please help.

(im trying to attach the excel file, but unable to, the attachment button not working)

Capture.PNG