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

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

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

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other" )))

Hope this helps.

Bill Horton

"Vick" wrote:

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

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

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

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=570030

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

Thanks William, but the answers keep saying "other"

"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other" )))

Hope this helps.

Bill Horton

"Vick" wrote:

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

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

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other ")))

Hope this helps.

Bill Horton

"Vick" wrote:

Thanks William, but the answers keep saying "other"

"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other" )))

Hope this helps.

Bill Horton

"Vick" wrote:

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

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

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

"SteveG" wrote:


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

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=570030

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

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


"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other ")))

Hope this helps.

Bill Horton

"Vick" wrote:

Thanks William, but the answers keep saying "other"

"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other" )))

Hope this helps.

Bill Horton

"Vick" wrote:

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

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

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(C OUNT(A6:A13)=4,"S","Other")))
But the trouble is achieving the letter "S"

"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other ")))

Hope this helps.

Bill Horton

"Vick" wrote:

Thanks William, but the answers keep saying "other"

"William Horton" wrote:

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,CO UNT(A6:A13)<=3),"I",IF(COUNT(A6:A13)=4,"S","Other" )))

Hope this helps.

Bill Horton

"Vick" wrote:

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

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

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=570030