If statement

[frykid50]

Okay, this is a strange one to me. I have 4 columns of sales numbers that
I'm looking at. If only 1 of these columns has sales (value greater than
zero) then I want to return "Single" in a 5th column. If 2 of the 4 columns
show sales, I want "double"; 3 of 4 is "triple", and 4 of 4 is a "homerun".

Since an if statement will only allow me to nest 3, any suggestions on a
formula to use?

Sample data
A B C D E
100 50 0 0
0 400 50 10

 

[Jacob Skaria]

Try
In E1
=CHOOSE(COUNTIF(A11,">0"),"Single","Double","Triple","Homerun")

If this post helps click Yes

 

[T. Valko]

Since an if statement will only allow me to nest 3

Actually, you can nest more depending on what version of Excel you're using.

However, here's a better way:

=CHOOSE(COUNTIF(A11,">0")+1,"","Single","Double","Triple","Home Run")

 

[Don Guillett]

=LOOKUP(COUNTIF(a5:d5,">0"),{0,1,2,3},{"","s","d","t"})

 

[Derrick]

how come you can only nest three? excel 2003 will nest up to 7 if statements.

also, try using a COUNTIF() function.
if(countif(A11>0)=1, "single", if(countif(A11>0)=2, "double",
if(countif(A11>0)=3, "Triple", if(countif(A11>0)=4, "homerun",
"Strikeout"))))

i think its possible to do the comparison array like so. otherwise you will
need another nested function to check if the values in the array are greater
than 0.

 

[frykid50]

Thank you! This works exactly like I was looking for