i have been trying so many methods to get this working and failed. Originally I was trying to use take the data from another sheet but since its not mandatory I copy and pasted the data in the same sheet which is "Destination" and "Pallets" They are 408 rows and no matter what I enter for the median if function I end up with #VALUE!. I tried using "Oakland CA" to search the correct item in the list and then switched it to search the B6 cell for example and so on. Can anyone provide a function that actually works? Also I am required to use the IF function. Thank you.

-MEDIAN(IF(SBS21:SBS428-A6,$C$21:$C$428)) Mean pallet shipment Median pallet shipment Mode pallet shipment Oakland CA Portland OR #VALUE! 22.2 19.0 6 Riverside Riverside CA #VALUE! Seattle WA 23.0 10 12 13 14 15 16 17 18 19 Was unable to create mean colum with average if function but I have yet to figure out how to obtain median and mode Destination Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Oakland CA Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR Pallets 21 1 1 1 23 24 1 26 27 28 29 30 31 32 1 35 36 37 38 1 1 M1 A-E Data | Costs&Distances MIA MIB MIC MID MIE MIFM2 A-EFSA-B