I am attempting to sum columns based on whether one column
contains text. When I use an If statement, it only works
part of the time, and the other part I receive the dreaded
#VALUE! since one column contains text. I have tried the
following formulas and they don't work in all conditions.
Can someone point me in the right direction?

Sample Data
Col C Col D
6 Not Rated
6 3
6 0

=SUM(IF(CODE($D31=78),$E27,SUM($C31-$D31)+$E27))
=SUM(IF(CODE($D31<78),($C31-$D31)+$E27),$E27)
=IF(LEN($D310),SUM(($C31-$D31)+$E27),$E27)
=SUM(IF(LEFT(D31="N",1),$E27,($C31-$D31)+$E27))
=IF(CODE($D32<78),(SUM($C32-$D32)+$E31),$E31)