How calc median, average, max based on condition? Like SUMIF kinda...

[somanyqs]

Say I want to know the average weight of our apples, and the data I have looks like this:

Col A Col B

Food Weight (oz)
Apple 6
Banana 12
Apple 9
Apple 5
Banana 11


Is there something like a SUMIF function to calculate the Average, Median and Max for the weight of the apples?

I know for average I could do SUMIF/COUNTIF to get the average, but didn't know if there was an easier way.
Also not sure how to do it at all for Median and Max...


Thanks!

 

[iliace]

=AVERAGE(IF(B1:B100>50,A1:A100))

Ctrl+Shift+Enter to confirm.

Will return the average of A1:A100, but only for those rows where B1:B100 is greater than fifty.

 

[Scott Huish]

=AVERAGE(IF(A2:A6="apple",B2:B6))
=MEDIAN(IF(A2:A6="apple",B2:B6))
=MAX(IF(A2:A6="apple",B2:B6))

all need to be confirmed with CTRL-SHIFT-ENTER rather than just Enter.

 

[somanyqs]

Thanks much!