Averageifs

[Deb115]

Please can anyone help?
If i have 3 columns in excel,, A and B and C
A contains unique names, B contains details about names in A and C contains values for each of those names in A
i want to get average of hte values in C dependant on the details in B.. so i might want an average of the values in column C, if B contains codes X Y OR Z

ie the average of C values if names in A have any one of the details X Y OR Z in B..
if i do averageifs,, i can get the average of column C if any name has X Y OR Z ( but they only have one code so the answer is always DIV0. And i know i could do it if the criteria X Y or Z were in separate columns but they aren’t

i Have spent hours trying to do this. I have used array formulae in past but i think that would also only give me the average of C values if all B criteria are fulfilled but they never are..

I hope you can save a bear of little brain many more hours.....thanks in advance
D
a

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...
=SUM(SUMIF(B:B,{"X";"Y";"Z"},C:C))/SUM(COUNTIF(B:B,{"X";"Y";"Z"}))

Hope this helps

M.

 

[Deb115]

Thanks.. will try that!

 

[Deb115]

Thank you..it worked but only if I copied your formula into my spreadsheet and then amended the ranges and criteria!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.