Average / sumproduct based on multiple criteria

[Anto111]

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

 

[Anto111]

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

 

[yshridhar]

=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

 

[Max]

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
---