Conditional SUMIF

[Curtis]

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

 

[Bernie Deitrick]

Curtis,

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

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

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

 

[Bernie Deitrick]

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

 

[Curtis]

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

 

[Curtis]

Thnaks

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

 

[Curtis]

Got it to work....Thanks

needed to fchange formatting in column $A

 

[Myrna Larson]

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

 

[Aladin Akyurek]

Remove comma before the >0 bit...

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


: