Superman,

You can even try this option if you want with SUMPRODUCT.

[ =SUMPRODUCT(($E$1:$E$31="Yes")*(ISNUMBER(SEARCH("*Teacher*",$F$1:$F$31)))) ]

[ SUMPRODUCT(($E$1:$E$31="Yes")*(ISNUMBER(SEARCH("*Teacher*",$F$1:$F$31)))*($D$1:$D$31="Purple")) ]

The ISNUMBER would help with getting the TRUE values with Wildcards...