Prev: Need help formatting rows
Next: how do I change format to percentage without multiplying by 10
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" <JEB(a)discussions.microsoft.com> 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?
>

 | 
Pages: 1
Prev: Need help formatting rows
Next: how do I change format to percentage without multiplying by 10