| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Summing (Sumif? Countif?) | Excel Worksheet Functions | |||
| copy the conditional sum (SUMIF) formula | Excel Worksheet Functions | |||
| adding summed cells in a conditional sumif | Excel Worksheet Functions | |||
| HOW DO I COPY A CONDITIONAL SUM (sumif) AND EDIT IT? (THE SUM NEV. | Excel Worksheet Functions | |||
| Conditional Format With SUMIF | Excel Worksheet Functions | |||
Linear Mode
