From: JEB on 14 Apr 2010 15:00 Now, what if I need to sum only the amounts in column "J3:J50" where there is no "#Value" displayed. "Jacob Skaria" wrote: > =SUMPRODUCT(--(A3:A50="Consumer"),--(J3:J50>0),--(J3:J50))/C55 > -- > > Here's my current formula: > > > > =SUMIF(A3:A50,"Consumer",J3:J50)/C55 > > > > This works fine to get an average sum for everything that reads Consumer, > > but now I need to sum only the positive \$ amounts in column "J3:J50". How and > > where do I insert this extra condition? From: Fred Smith on 14 Apr 2010 18:44 Fix the cells which are displaying #Value. Typically done with: =if(iserror(yourcalculation),0,yourcalculation) Regards, Fred "JEB" wrote in message news:06F9F4B0-927F-4B0B-8A4A-95DE38FE5AAF(a)microsoft.com...> > Now, what if I need to sum only the amounts in column "J3:J50" where there > is no "#Value" displayed. > > > "Jacob Skaria" wrote: > >> =SUMPRODUCT(--(A3:A50="Consumer"),--(J3:J50>0),--(J3:J50))/C55 >> -- >> > Here's my current formula: >> > >> > =SUMIF(A3:A50,"Consumer",J3:J50)/C55 >> > >> > This works fine to get an average sum for everything that reads >> > Consumer, >> > but now I need to sum only the positive \$ amounts in column "J3:J50". >> > How and >> > where do I insert this extra condition? >  |