|re: "it returns 0 because I assume it's looking at the formula in the column and not the results of the formula in the column"|
Your assumption is incorrect. What good would formulas be if you couldn't use the values that they return in other calculations?
It is returning 0 because you used quotes around your values:
Excel interprets anything within quotation marks to be text, not numbers, and you can't SUM text values.
In other words, "1" is not the same as 1.
If you use this instead, the SUM formula will work:
=IF(Sheet1!B81="Yes", 1, 0),
That said, Mike's suggestion of using COUNTIF to eliminate the IF function is certainly more efficient, unless of course you need the 1's and 0's for something else.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.