Formula help needed

**JMM** · 02-20-2005, 03:12 PM

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.

Please Login or Register  to view this content.

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)

**duane** · 02-20-2005, 03:46 PM

FIRST FORMULA

=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"))))

THE SECOND REQUEST IS A LITTLE MORE COMPLEX............

**JMM** · 02-20-2005, 03:55 PM

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

**duane** · 02-20-2005, 04:03 PM

For This One, I Think It Requires Summing To Exclude Duplicates

{=sum(($b$1:$b$8="ce")*($e$1:$e$8="ns"))+sum(($b$1:$b$8="ce")*($f$1:$f$8="ns")*($e$1:$e$8<>"ns"))+sum(($b$1:$b$8="ce")*($g$1:$g$8="ns")*($e$1:$f$8<>"ns"))+sum(($b$1:$b$8="ce")*($h$1:$h$8="ns")*($e$1:$g$8<>"ns"))+sum(($b$1:$b$8="ce")*($i$1:$i$8="ns")*($e$1:$g$8<>"ns"))}

This Is An Array Formula

**duane** · 02-20-2005, 04:08 PM

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"))))

**JMM** · 02-20-2005, 04:18 PM

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?

**JMM** · 02-20-2005, 04:20 PM

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!

**duane** · 02-20-2005, 04:40 PM

unfortunately it looks like the individual columns need to be examined

{=sum(($b$1:$b$8="ce")*($e$1:$e$8="ns"))+sum(($b$1:$b$8="ce")*($f$1:$f$8="ns")*($e$1:$e$8<>"ns"))+sum(($b$1:$b$8="ce")*($g$1:$g$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns"))+sum(($b$1:$b$8="ce")*($h$1:$h$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns")*($g$1:$g$8<>"ns"))+sum(($b$1:$b$8="ce")*($i$1:$i$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns")*($g$1:$g$8<>"ns")*($h$1:$h$8<>"ns"))}

more easy to see the pieces

ce in b, ns in e

{=sum(($b$1:$b$8="ce")*($e$1:$e$8="ns"))+

ce in b, ns in f but not in e

sum(($b$1:$b$8="ce")*($f$1:$f$8="ns")*($e$1:$e$8<>"ns"))+

ce in b, ns in g but not in e or f

sum(($b$1:$b$8="ce")*($g$1:$g$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns"))+

ce in b, ns in h but not in e, f or g

sum(($b$1:$b$8="ce")*($h$1:$h$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns")*($g$1:$g$8<>"ns"))+

ce in b, ns in i but not in e, f, g or h

sum(($b$1:$b$8="ce")*($i$1:$i$8="ns")*($e$1:$e$8<>"ns")*($f$1:$f$8<>"ns")*($g$1:$g$8<>"ns")*($h$1:$h$8<>"ns"))}

**JMM** · 02-20-2005, 04:53 PM

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.

**Domenic** · 02-20-2005, 05:26 PM

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!

**olasa** · 02-22-2005, 08:09 PM

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

**JMM** · 02-23-2005, 01:56 PM

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?

**duane** · 02-23-2005, 11:18 PM

the formula is looking for

1) "ns" in column e:i
2) sum of column e:i >0

barring this it results in zero

=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"))))

if zeroes count and should not result in "no Apps"

I think this should do it

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