To use a dynamic range, and based on Xor's example ( as you did not provide a sample hseet), select B2:C21 -Click - Insert - click Table - click " My table has headers " (or not) - Ok

Realign Xor's result range
Now when you enter more data the formula will adapt automatically
Beware that when you enter a "yes" in the first column and nothing in the second, the function will recalculate based on a zero value for col C


To avoid this use =IF(OR(B3="",C3=""),"",AVERAGEIF($B$3:B3,"yes",$C$ 3:C3))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post