I have reference data in Column "$Q$3:$Q$502" of "sheet1". For all cells in
the reference column that contain "yes", I would like to average quantity in
cells for Column $S$3:$S$502 respectively.

So if it finds 3 in column Q with "Yes" (2,3,4) , it will return the number 3.
--
Thank you!