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$65 536)

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$655360))

HTH,
Bernie
MS Excel MVP


"Curtis" wrote in message
...
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$65 536)

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" wrote in message
...
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$65 536)

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$655360)*JAN_05'!J$2:$J$65536)


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Curtis,

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

HTH,
Bernie
MS Excel MVP


"Curtis" wrote in message
...
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$65 536)

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" wrote in message
...
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$65 536)

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$655360)*JAN_05'!J$2:$J$65536)


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Curtis,

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

HTH,
Bernie
MS Excel MVP


"Curtis" wrote in message
...
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$65 536)

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$655360)*JAN_05'!J$2:$J$65536)


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Curtis,

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

HTH,
Bernie
MS Excel MVP


"Curtis" wrote in message
...
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$65 536)

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
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" wrote in message
...
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$65 536)

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" wrote in message
...

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