calculate average in a column based on criteria in another column

I want to calculate the average of numbers in one column based on crieteria
in another colume.

If the value in column A = x, sum and average the corresponding values in
column B

=AVERAGE(IF(A1:A20="x",B1:B20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"sharon t" <sharon [email protected]> wrote in message
news:[email protected]...
> I want to calculate the average of numbers in one column based on
crieteria
> in another colume.
>
> If the value in column A = x, sum and average the corresponding values in
> column B

Here are 2 thoughts:

For a value to be matched in D1
and data in A1:B10

Try this:
C1: =SUMIF(A1:A10,D1,B1:B10)/COUNTIF(A1:A10,D1)

or this ARRAY FORMULA*:
=AVERAGE(IF(A1:A10=D1,B1:B10))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sharon t" wrote:

> I want to calculate the average of numbers in one column based on crieteria
> in another colume.
>
> If the value in column A = x, sum and average the corresponding values in
> column B