AVERAGE IF in array not working

[WildWill]

Hi

I have the following data:

A B
22 70%
29 20%
22 90%
24 10%

I want to calculate the AVERAGE of the values in Column B, where Column A's
value is "22". I.e. the answer to the above example will be 80%. I have tried
the {=AVERAGE(IF(A1:A4="22",B1:B4))} but it does not work?

 

[Mike H]

Hi,

No quotes around the 22 and array enter#

=AVERAGE(IF(A1:A4=22,B1:B4))

Mike

 

[Don Guillett]

Try it without "" and make sure you enter using ctrl+shift+enter
=AVERAGE(IF(A1:A4=22,B1:B4))

 

[WildWill]

Thanks Mike H!

Considering the following derivative, please advise:

A B
22 Cat
29 Bat
22 Tax
24 Bat
22 Cat

I want to calculate the total number (COUNT) of the occurences of "Cat" in
Column B, where where Column A's value is "22". I.e. the answer to the above
example will be 2.

 

[WildWill]

Thanks Don!

Try it without "" and make sure you enter using ctrl+shift+enter
=AVERAGE(IF(A1:A4=22,B1:B4))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)