Averageif / Average Or for Multiple Conditions in the Same Column

[apmale]

Hi all,

I'm fairly well versed in functions and array formulas and am surprised that I'm just running into the issue below for the first time. I want to average the data in 'column b' only if the qualifiers 'x' and 'y' are met in 'column a'. For example

Column A Column B
Red 2
Red 4
Yellow 5
Yellow 7
Blue 8
Green 10

I want the average of 'column b' data that corresponds to 'red' and 'yellow'. The answer should be 4.5. I've tried several 'averageif' formulas with 'and' and 'or'. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta(if yellow) + counta(if red)). I am thinking that there may be a simpler way, which would be helpful with my more complicated model.

Thanks!

 

[Michael M]

Afraid mine might not be much better

Excel 2003
	A	B
1	Column A	Col B
2	Red	2
3	Red	4
4	Yellow	5
5	Yellow	7
6	Blue	8
7	Green	10
8
9		4.5
Sheet1

Cell Formulas
Range	Formula
B9	=SUMPRODUCT(--(A2:A7="Red")--(A2:A7="Yellow"),B2:B7)/(COUNTIF(A2:A7,"Red")+COUNTIF(A2:A7,"Yellow"))

 

[Weazel]

Hi all,

I'm fairly well versed in functions and array formulas and am surprised that I'm just running into the issue below for the first time. I want to average the data in 'column b' only if the qualifiers 'x' and 'y' are met in 'column a'. For example

Column A Column B
Red 2
Red 4
Yellow 5
Yellow 7
Blue 8
Green 10

I want the average of 'column b' data that corresponds to 'red' and 'yellow'. The answer should be 4.5. I've tried several 'averageif' formulas with 'and' and 'or'. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta(if yellow) + counta(if red)). I am thinking that there may be a simpler way, which would be helpful with my more complicated model.

Thanks!

Probably not the best way but you can try...

 =AVERAGE(IF((N2:N7={"Red","Yellow"}),O2:O7))

you'll need to adjust the ranges

Entered with CTRL+SHIFT+ENTER

 

[Aladin Akyurek]

Hi all,

I'm fairly well versed in functions and array formulas and am surprised that I'm just running into the issue below for the first time. I want to average the data in 'column b' only if the qualifiers 'x' and 'y' are met in 'column a'. For example

Column A Column B
Red 2
Red 4
Yellow 5
Yellow 7
Blue 8
Green 10

I want the average of 'column b' data that corresponds to 'red' and 'yellow'. The answer should be 4.5. I've tried several 'averageif' formulas with 'and' and 'or'. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta(if yellow) + counta(if red)). I am thinking that there may be a simpler way, which would be helpful with my more complicated model.

Thanks!

Control+shift+enter,

=AVERAGE(IF(ISNUMBER(MATCH(A2:A7,{"red","yellow"},0)),B2:B7))

With the criterion values in E2:E3...

=AVERAGE(IF(ISNUMBER(MATCH(A2:A7,E2:E3,0)),B2:B7))

 

[apmale]

Thank you, guys! Great answers. Much appreciated.