Another IF(COUNT Formula Question.

[justine]

The formula you gave was perfect, thanks!
Now, I have another one.

I have a weekly total sheet that uses this formual.

=IF(SUM('Daily Lates'!L4)=0,"",SUM('Daily Lates'!L4))

However, I want to add the following to the above formula.

I want to put N/A if the sum says N/A. How can I add that to the above formula?

Thanks for all your help!!

 

[Ian Mac]

On 2002-04-15 07:23, justine wrote:
The formula you gave was perfect, thanks!
Now, I have another one.

I have a weekly total sheet that uses this formual.

=IF(SUM('Daily Lates'!L4)=0,"",SUM('Daily Lates'!L4))

However, I want to add the following to the above formula.

I want to put N/A if the sum says N/A. How can I add that to the above formula?

Thanks for all your help!!

Firstly, I don't think you need the SUM() part, so try.

=IF(Daily Lates!L4<>0,Daily Lates!L4,"")

this should do the trick, I might be making assumptions but are the N/A's in the forula in L4 placed in by an IF() like =IF(Your formula,When True,"N/A"),

if I'm right change the the "N/A" part to NA() which will generate a #N/A
and will therefore be included in the value return, instead of 0, which is what I think is happening.

Also, By taking out the SUM() it'll just return the value OR text string in the cell L4. but I'd still use NA().

Confused I am

 

[justine]

No, the N/A comes from this formula.
=IF(COUNTIF(E4:K4,"Quit")>0,"N/A",IF(COUNTIF(E4:K4,"Inj")>0,"N/A",IF(COUNTIF(E4:K4,"NS")>0,COUNTIF(E4:K4,"NS"),"")))

We enter info on the other sheet with the formula i gave you earlier, I just need a formual added within this formua one that accounts for the N/A. Can I do this?

 

[Aladin Akyurek]

On 2002-04-15 07:23, justine wrote:
The formula you gave was perfect, thanks!
Now, I have another one.

I have a weekly total sheet that uses this formual.

=IF(SUM('Daily Lates'!L4)=0,"",SUM('Daily Lates'!L4))

However, I want to add the following to the above formula.

I want to put N/A if the sum says N/A. How can I add that to the above formula?

Thanks for all your help!!

=IF('Daily Lates'!L4,'Daily Lates'!L4,"")

should be sufficient.

 

[Ian Mac]

On 2002-04-15 07:52, justine wrote:
No, the N/A comes from this formula.
=IF(COUNTIF(E4:K4,"Quit")>0,"N/A",IF(COUNTIF(E4:K4,"Inj")>0,"N/A",IF(COUNTIF(E4:K4,"NS")>0,COUNTIF(E4:K4,"NS"),"")))

We enter info on the other sheet with the formula i gave you earlier, I just need a formual added within this formua one that accounts for the N/A. Can I do this?

My suggestion was to change the original formula to:

=IF(COUNTIF(E4:K4,"Quit")>0,NA(),IF(COUNTIF(E4:K4,"Inj")>0,NA(),IF(COUNTIF(E4:K4,"NS")>0,COUNTIF(E4:K4,"NS"),"")))

if you try to SUM() an ERROR it will return the ERROR, if you try to SUM() text it will return 0.

 

[justine]

Thank you, that was exactly what I needed.