Formula help needed

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)

First formula says there are ( or ) missing. When I try to fix, it then tells me I have too many parameters.

Originally Posted by duane

this should fix it - ) before ,"No Apps" and I missed one on the countif

=IF(AND(ISNA(MATCH("NS",E1:I1,0)),SUM(E1:I1)=0),"No Apps",SUM(E1:I1)/(C1*(COUNTIF(E1:I1,">=0")+COUNTIF(E1:I1,"NS"))))

It WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!

Yeah!!! To me that was the hard one, the one I HAD to get.

Thank you thank you thank you!

That works!

Thank you again. Not only did you help solve my problem, but you helped me learn more about how to do this next time. Now that I can see it I can figure out how to get there. Again - Thank you.

When I put this in my sheet it gives an answer of 20.

13 = the total number of NS in any CE row
5 is the answer I need to get
there are 9 CE rows

I can't figure out where it gets 20 from.

This is so hard to explain and figure out without the actual sheet. Would you like me to email it to you?

Originally Posted by JMM

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)

Try the following array formula...

=SUM(IF((B1:B8="CE")*((E1:E8="NS")+(F1:F8="NS")+(G1:G8="NS")+(H1:H8="NS")+(I1:I8="NS")),1,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

And just because it's possible...
=SUM(MMULT((B1:B8="CE")*(E1:I8="NS"),{1,1,1,1})>0)
Ola Sandstrom

Ok - now I have the formulas Duane sent me into the sheet at work and a new problem has surfaced. I have one person who has - <blank> <blank> 0 End <blank> and it is not calculating this out to a 0% in column D. It is giving me "No Apps" instead. I have doubl checked to make sur ethat it is zero and not Oh (since I do not do the data entry). I also checked to make sure that the formula had not been accidentially messed up. What am I missing here?

That fixed it. Thank you so much for all your help.