in excel 2003 a formula if cells have 0 = n 1-3 =s 4ormore=s

[Guest]

Excel 2003 I need to create a formula that would count each cell with a
number in it as 1 and assign a letter
if cells adds up to 0 = N
if cells adds up 1-3 = I
if cells adds up to 4 = S

 

[Guest]

Try the following formula (which assumes your range of cells you want checked
is A6:A13.

=IF(COUNT(A6:A13)=0,"N",IF(AND(COUNT(A6:A13)>=1,COUNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other")))

Hope this helps.

Bill Horton

 

[SteveG]

Vick,

Try,

=IF(COUNT(A1:A5)=0,"N",IF(COUNT(A1:A5)<=3,"I","S"))

A1:A5 is your range to count.

COUNT will also count dates if they appear in your range.


HTH

Stev

 

[Guest]

Thanks William, but the answers keep saying "other"

 

[Guest]

If you keep getting OTHER it means that the count must be greater than 4. If
you want it to be S if it is 4 or greater change the formula to:

=IF(COUNT(A6:A13)=0,"N",IF(AND(COUNT(A6:A13)>=1,COUNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)>=4,"S","Other")))

Hope this helps.

Bill Horton

 

[Guest]

Thanks Steve, I am almost there. I need to have it count the cells that have
a number greater than 0 and then count the cells as one and then assign the
following. If the cells have all 0 I need it to = N
1-3 = I
3-4 = S
0 = N

 

[Guest]

Thanks Williams, I am almost there. I need to have it count the cells that
have
a number greater than 0 and then count each cell as 1 and then assign the
following. If the cells have all 0 I need it to = N
1-3 cells with a number greater than 0 = I
3-4 cells with a number greater than 0 = S
all 0 in each cell = N

 

[Guest]

I changed =if(sum(d6:g6)=0,'N"
that gives me the right letter

IF(AND(COUNT(A6:A13)>=1,COUNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)>=4,"S","Other")))
But the trouble is achieving the letter "S"

 

[SteveG]

Vick,

Try this,


=IF(COUNT(A6:A13)=COUNTIF(A6:A13,0),"N",IF(COUNTIF(A6:A13,">0")<=4,CHOOSE(COUNTIF(A6:A13,">0"),"I","I","I","S"),"Other"))

HTH

Steve