Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Another IF(COUNT Formula Question.

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    "Have a good time......all the time"
    Ian Mac

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    "Have a good time......all the time"
    Ian Mac

  6. #6
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you, that was exactly what I needed.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •