Finding the median of numbers meeting criteria

[thekovinc]

I have a problem I would love some help with!

I have about 100 numbers in a column. I also have criteria in other
columns that classify the numbers into groups. I am curious if there
is any way to find the median of only SOME of the numbers in the
column.

For example:

A B C
city street 1600
city avenue 1400
town street 1500
village avenue 1700
city street 1900
city street 1200

Is there any formula I could put in a cell that would let me find the
median of the values of column C that meet the criteria of having
column A=city and column B = street (would be the median of 1600,
1900, and 1200, and would result in 1600)?

I have tried using sumproducts for the criteria aspect of it, but I
quickly got stuck.

If anyone has any ideas, I would love to hear them!

Thanks for your time.

 

[Domenic]

Try...

=MEDIAN(IF(A1:A6="City",IF(B1:B6="Street",C1:C6)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Dave Peterson]

=MEDIAN(IF((A1:A6="city")*(B1:B6="street"),C1:C6))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[thekovinc]

Thanks! That worked perfectly!