Hi guys,

I have 3 columns. Column A contains the category lables "cat",
"dog","goldfish","mouse", column B contains a further category lable, ie 1,
2, 3, or 4 depending on their assigned grade, and column C contains a
variable value.

I need to average the variable values in column C only if they meet two of
the specific criteria in Columns A and B.

For example only average the values in column C where Column A contains cat
and column B contains the number 1.

The people on this forum have been invaluable to me for my problems with
excel so many thanks in advance.

kind regards,

Ant

Sorry, I forgot to add.... the criteria number to be used from column B is
stipulated in cell A2. So for example if cell A2 contains the number 1 then I
need the average of the values where column A cotains "Cat" and column B
contains 1. If cell A2 contains the number 2 I need the average of the values
where column A cotains "Cat" and column B contains the number 2, and so forth.

Sorry for this oversight, and many thanks again.

Kind regards,

Anthony

"Anto111" wrote:

Hi guys,

I have 3 columns. Column A contains the category lables "cat",
"dog","goldfish","mouse", column B contains a further category lable, ie 1,
2, 3, or 4 depending on their assigned grade, and column C contains a
variable value.

I need to average the variable values in column C only if they meet two of
the specific criteria in Columns A and B.

For example only average the values in column C where Column A contains cat
and column B contains the number 1.

The people on this forum have been invaluable to me for my problems with
excel so many thanks in advance.

kind regards,

Ant

=AVERAGE(IF((A4:A12="cat")*(B4:B12=A2),C4:C12))
It is an *array* formula. Enter it with Ctrl+Shift+enter.
Change the ranges according to your requirement.
Best wishes
Sreedhar
"Anto111" wrote:

Sorry, I forgot to add.... the criteria number to be used from column B is
stipulated in cell A2. So for example if cell A2 contains the number 1 then I
need the average of the values where column A cotains "Cat" and column B
contains 1. If cell A2 contains the number 2 I need the average of the values
where column A cotains "Cat" and column B contains the number 2, and so forth.

Sorry for this oversight, and many thanks again.

Kind regards,

Anthony

"Anto111" wrote:

Hi guys,

I have 3 columns. Column A contains the category lables "cat",
"dog","goldfish","mouse", column B contains a further category lable, ie 1,
2, 3, or 4 depending on their assigned grade, and column C contains a
variable value.

I need to average the variable values in column C only if they meet two of
the specific criteria in Columns A and B.

For example only average the values in column C where Column A contains cat
and column B contains the number 1.

The people on this forum have been invaluable to me for my problems with
excel so many thanks in advance.

kind regards,

Ant

One way

Assume data within A3:C8, with criteria for col B in A2 (as posted)

In D3, array-entered*:
=AVERAGE(IF((A$3:A$8=A3)*(B$3:B$8=$A$2),C$3:C$8))
Copy down

If you need an error-trap to return blanks,
use this in D3, array-entered*:
=IF(ISERROR(AVERAGE(IF((A$3:A$8=A3)*(B$3:B$8=$A$2) ,C$3:C$8))),"",AVERAGE(IF((A$3:A$8=A3)*(B$3:B$8=$A $2),C$3:C$8)))

*To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula

Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Anto111" wrote:
Sorry, I forgot to add.... the criteria number to be used from column B is
stipulated in cell A2. So for example if cell A2 contains the number 1 then I
need the average of the values where column A cotains "Cat" and column B
contains 1. If cell A2 contains the number 2 I need the average of the values
where column A cotains "Cat" and column B contains the number 2, and so forth.

Sorry for this oversight, and many thanks again.

Kind regards,

Anthony

"Anto111" wrote:

Hi guys,

I have 3 columns. Column A contains the category lables "cat",
"dog","goldfish","mouse", column B contains a further category lable, ie 1,
2, 3, or 4 depending on their assigned grade, and column C contains a
variable value.

I need to average the variable values in column C only if they meet two of
the specific criteria in Columns A and B.

For example only average the values in column C where Column A contains cat
and column B contains the number 1.

The people on this forum have been invaluable to me for my problems with
excel so many thanks in advance.

kind regards,

Ant