Using an IF Function with 3 returns?

The title probably doesn't explain it well but this is what i have so far but think i may be well out

This is what i need;

C49, D49, E49 will show either "Promoter", "Passive" or "Detractor"
I have already worked that when you do
=COUNTIF(C49:E49,"Promoter")-COUNTIF(C49:E49,"Detractor")
it will show a number, which is great

now i need to add to that so
Returns of 1 or higher will show the word "Promoter"
Returns of 0 will show the word "Passive"
Returns of -1 or less will show the word "Detractor"


does anyone know how i would amend the more simple

COUNTIF(C49:E49,"Promoter")-COUNTIF(C49:E49,"Detractor")

to make it take into account that the cell will show "Promoter", "Passive" or "Detractor" as an end result?
I can only seem to get it to show "Promoter" as 1 or more... or "Detractor" as 0 or less with the following (but can't seem to work out how to add the 3rd option)

=IF((COUNTIF(C49:E49,"Promoter")-COUNTIF(C49:E49,"Detractor"))>=0,"Promoter","Detractor")


Grateful for any help, cheers guys

Try this
=IF((COUNTIF(C49:E49,"Promoter")-COUNTIF(C49:E49,"Detractor"))>0,"Promoter",IF((COUNTIF(C49:E49,"Promoter")-COUNTIF(C49:E49,"Detractor"))<0,"Detractor","Passive"))

That's brilliant thankyou,
is there any way to have it so that if the text is blank in all of C49, D49 and E49 it will just be a blank cell or will say N/A?
otherwise all of my staff will show in the Passive section

IF is a boolean function; you choose one of two options pretty much by the way it's designed.

That said, you just wrap another IF around what already exists to do another test "upstream" of what you have.

=IF( COUNTIF(C49:E49,"Promoter")+COUNTIF(C49:E49,"Detractor")=0, "", existing_IFs)

You can have up to seven IFs total wrapped like that in 2003.

Thanks both of you,
I didn't realise you could do more than one IF, never had to do that before but now I know I will never forget

Originally Posted by LiveAptly.com

Whatever the limit is it is not worth knowing.

In 2007+ the limit is 64. (Maybe more in 2013?)

In 2003 or earlier, however, it's seven.

I had to look up the 2007 value; but the 2003 one is enough of a problem that it's a well-known limitation when constructing booleans in excel.

Well

"Well known" to people who hang out on excel help forums answering questions, that is, so that's a pretty serious filter on "well known."

Yes, 64 nested functions is a bit more than I can keep track of
a limit of 6 would be pretty anoying - fortunately I was in my mid teens back in 03 and not too concerned about nested functions