Prev: Convert Week number into Month
Next: sales tax chart
From: Rene on 27 May 2010 20:04
SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200))/SUMPRODUCT(--(S498:S534="a"),--(G498:G534<>""))

I searched questions already answered but did not see averageif with 3
criteria

I want the formula to return the average. Thanks in advance
From: T. Valko on 27 May 2010 21:17
>I want the formula to return the average.

The average of what?

It looks like you're calculating the percentage of cells that meet a
condition. Why do you think you need/want an "averageif" formula?

What version of Excel are you using?

--
Biff
Microsoft Excel MVP


"Rene" <Rene(a)discussions.microsoft.com> wrote in message
news:444DC66A-0FCC-474E-9345-430E340E1BA8(a)microsoft.com...
> SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200))/SUMPRODUCT(--(S498:S534="a"),--(G498:G534<>""))
>
> I searched questions already answered but did not see averageif with 3
> criteria
>
> I want the formula to return the average. Thanks in advance


From: Ashish Mathur on 28 May 2010 19:38
Hi,

Try this

=SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200)*(G498:G534))/SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rene" <Rene(a)discussions.microsoft.com> wrote in message
news:444DC66A-0FCC-474E-9345-430E340E1BA8(a)microsoft.com...
> SUMPRODUCT(--(S498:S534="a"),--(G498:G534>=100),--(G498:G534<=200))/SUMPRODUCT(--(S498:S534="a"),--(G498:G534<>""))
>
> I searched questions already answered but did not see averageif with 3
> criteria
>
> I want the formula to return the average. Thanks in advance

 | 
Pages: 1
Prev: Convert Week number into Month
Next: sales tax chart