If statement

  • Thread starter Thread starter frykid50
  • Start date Start date
F

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
 
Try
In E1
=CHOOSE(COUNTIF(A1:D1,">0"),"Single","Double","Triple","Homerun")

If this post helps click Yes
 
Since an if statement will only allow me to nest 3
Click to expand...

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

However, here's a better way:

=CHOOSE(COUNTIF(A1:D1,">0")+1,"","Single","Double","Triple","Home Run")
 
how come you can only nest three? excel 2003 will nest up to 7 if statements.

also, try using a COUNTIF() function.
if(countif(A1:D1>0)=1, "single", if(countif(A1:D1>0)=2, "double",
if(countif(A1:D1>0)=3, "Triple", if(countif(A1:D1>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.
 
Back
Top