Conditional SUMIF

I am currently using the formulae to clcualte the sum for $A5

=SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)

I would to modify this so it leaves out all numbers less than 0

Thanks

Curtis,

=SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0))

HTH,
Bernie
MS Excel MVP


"Curtis" <[email protected]> wrote in message
news:[email protected]...
>I am currently using the formulae to clcualte the sum for $A5
>
> =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
>
> I would to modify this so it leaves out all numbers less than 0
>
> Thanks

Use SUMPRODUCT
=SUMPRODUCT(--(JAN_05'!$C$2:$C$65536=$A5),--(JAN_05'!$C$2:$C$65536,>0),JAN_05'!J$2:$J$65536)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Curtis" <[email protected]> wrote in message
news:[email protected]...
>I am currently using the formulae to clcualte the sum for $A5
>
> =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
>
> I would to modify this so it leaves out all numbers less than 0
>
> Thanks

Oops, forgot to actually sum:

=SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0)*JAN_05'!J$2:$J$65536)


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Curtis,
>
> =SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Curtis" <[email protected]> wrote in message
> news:[email protected]...
>>I am currently using the formulae to clcualte the sum for $A5
>>
>> =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
>>
>> I would to modify this so it leaves out all numbers less than 0
>>
>> Thanks
>
>

It gives me " The formula you typed contains an error" message. FYI the sum
of number greater than zero is in column J not c...Sorry but that should not
be the difference.

Thanks

ce


"Bernard Liengme" wrote:

> Use SUMPRODUCT
> =SUMPRODUCT(--(JAN_05'!$C$2:$C$65536=$A5),--(JAN_05'!$C$2:$C$65536,>0),JAN_05'!J$2:$J$65536)
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Curtis" <[email protected]> wrote in message
> news:[email protected]...
> >I am currently using the formulae to clcualte the sum for $A5
> >
> > =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
> >
> > I would to modify this so it leaves out all numbers less than 0
> >
> > Thanks
>
>
>

Thnaks

But this leaves the sums blank for all values in column c that contain a 0

"Bernie Deitrick" wrote:

> Oops, forgot to actually sum:
>
> =SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0)*JAN_05'!J$2:$J$65536)
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%[email protected]...
> > Curtis,
> >
> > =SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0))
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Curtis" <[email protected]> wrote in message
> > news:[email protected]...
> >>I am currently using the formulae to clcualte the sum for $A5
> >>
> >> =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
> >>
> >> I would to modify this so it leaves out all numbers less than 0
> >>
> >> Thanks
> >
> >
>
>
>

Got it to work....Thanks

needed to fchange formatting in column $A

"Bernie Deitrick" wrote:

> Oops, forgot to actually sum:
>
> =SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0)*JAN_05'!J$2:$J$65536)
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%[email protected]...
> > Curtis,
> >
> > =SUMPRODUCT((JAN_05'!$C$2:$C$65536=$A5)*(JAN_05'!J$2:$J$65536>0))
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Curtis" <[email protected]> wrote in message
> > news:[email protected]...
> >>I am currently using the formulae to clcualte the sum for $A5
> >>
> >> =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
> >>
> >> I would to modify this so it leaves out all numbers less than 0
> >>
> >> Thanks
> >
> >
>
>
>

Try inserting an apostrophe before each occurrence of JAN_05, so, e.g. the
first one becomes 'JAN_05'!$C$2:$C$65536

On Mon, 26 Sep 2005 12:31:01 -0700, Curtis <[email protected]>
wrote:

>It gives me " The formula you typed contains an error" message. FYI the sum
>of number greater than zero is in column J not c...Sorry but that should not
>be the difference.
>
>Thanks
>
>ce
>
>
>"Bernard Liengme" wrote:
>
>> Use SUMPRODUCT
>>
=SUMPRODUCT(--(JAN_05'!$C$2:$C$65536=$A5),--(JAN_05'!$C$2:$C$65536,>0),JAN_05'!J$2:$J$65536)
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Curtis" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am currently using the formulae to clcualte the sum for $A5
>> >
>> > =SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
>> >
>> > I would to modify this so it leaves out all numbers less than 0
>> >
>> > Thanks
>>
>>
>>

Remove comma before the >0 bit...

Curtis wrote:
> It gives me " The formula you typed contains an error" message. FYI the sum
> of number greater than zero is in column J not c...Sorry but that should not
> be the difference.
>
> Thanks
>
> ce
>
>
> "Bernard Liengme" wrote:
>
>
>>Use SUMPRODUCT
>>=SUMPRODUCT(--(JAN_05'!$C$2:$C$65536=$A5),--(JAN_05'!$C$2:$C$65536,>0),JAN_05'!J$2:$J$65536)
>>
>>--
>>Bernard V Liengme
>>www.stfx.ca/people/bliengme
>>remove caps from email
>>
>>"Curtis" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>I am currently using the formulae to clcualte the sum for $A5
>>>
>>>=SUMIF(JAN_05'!$C$2:$C$65536,$A5,JAN_05'!J$2:$J$65536)
>>>
>>>I would to modify this so it leaves out all numbers less than 0
>>>
>>>Thanks
>>
>>
>>