SUMSQ with multiple values

Yesterday, we found that =SUMSQ(B1:B20-$C$1) works well; it subtracts the constant from each value in B1:B20, squares each difference, and reports the total. I now need to subtract the constant from values that are not grouped together in an array. I tried =SUMSQ(VLOOKUP("yes",A1:B20,2)-$C$1) but it didn't work. Any suggestions?

Hello John,

Thank you for your reply. Sorry I was unclear before.

Cells A1:A10 contain the labels "yes" and "no" in random order. Cells B1:B10 contain values. Cell C1 contains a constant.
I need a single-cell formula, not a third column or an array, that gives me the sum of the squares of the "yes" items after the constant has been subtracted.
If the "yes" values were all in cells A1:A5, I could use =SUMSQ(B1:B5-$C$1), but my "yes" values are mixed in with the "no" values.

I don't think VLOOKUP, with either a TRUE or a FALSE option, will work. Other functions I have tried include COUNTIF and SUMIF. Any ideas?

Thanks,
Jim

Thanks John, =SUMSQ(IF(A1:A20="yes",B1:B20-$C$1,0)) works! Ctrl+Shift+Enter is not needed.