Ok, I am sure there is an easy solution - I just don't know enough about Excel to know what it is.

Here is a sample of the sheet I am working with. All columns except D are data entry. 
A         B    C         D         E         F         G         H         I
Andrew    CE   10        No Apps             New       NS        NS        NS
Bob       CE   10         50.0%    New       5         NS        NS        End	
Chuck     CE   10         30.0%    New       5         NS        NS        NS	
David     LL    5         33.3%              New       0         0         5	
Ed        LL    3         66.0%    NS        2         NS        NS        End
Fred      LL   10        100.0%    10        10        10        10        10
George    SM   10         40.0%    NS        3         5         End	
Harry     SM   10         20.0%    2         NS        End
Column D contains the formula -
{=IF(SUM(E1:I1)=0,"No Apps",SUM(E1:I1)/(C1*(COUNTIF(E1:I1,">=0"))))}

I need to change this so that it treats NS as a 0 (while ignoring the other text) without getting the pesky "divide by zero" error. Here is what D should give me -
Andrew - 0.0% (0+0+0/30)
Bob - 16.6% (5+0+0/30)
Chuck - 12.5% (5+0+0+0/40)
Ed - 16.6% (0+2+0+0/12)


Also I need to find the number of rows that have one or more NS.
The current formula - {=SUM(($B$1:$B$8="CE")*($E$1:$I$8="NS"))} would give me 8 in the above sample. It should give me 3 (Andrew, Bob, Chuck are CE & have 1+ NS)