For others cross posted at: http://www.excelforum.com/excel-form...-criteria.html
For chon, some light reading: http://www.excelguru.ca/content.php?184
It would be nice of you to include links to all cross posts at all sites. (I for one, refrain from helping a cross poster a second time if they don't.)
These two formula are to be array-entered (ctrl+shift+enter, not just enter):
=MEDIAN(IF($D$3:$D$20="Australia",IF(LEN($G$3:$G$20)=0,FALSE,$G$3:$G$20)))
=MEDIAN(IF($D$3:$D$20="Australia",IF(LEN($G$3:$G$20)=0,0,$G$3:$G$20)))
the blue 0 formula counts blanks as zeroes, the blue FALSE formula disregards blanks in the median calculation. (In your sample data, the results are the same.)
Bookmarks