Results 1 to 7 of 7

Thread: ISERR with COUNTIFS

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ISERR with COUNTIFS

    Hi All

    I have:

    =SUMPRODUCT(--ISERROR(Dates!F6:F7000)
    &
    =COUNTIF(Dates!F6:F7000,"Car"
    Can they be combined in one countifs expression at all?

    Many thanks

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: ISERR with COUNTIFS

    What is it that you are actually trying to do?
    Count the "Car" entries"
    Exatly what kind of errors do you have in this list?
    Do you know what is causing them (formulas, etc)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISERR with COUNTIFS

    Hi Joe4,
    I am looking to count the number of #VALUE errors in Column F, that have "Car" in Column C. The errors are expected because a date hasn't been inputted in another sheet, and I want to count the number of missing dates.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: ISERR with COUNTIFS

    Can you update your formulas in column F to account for the error, and return something else instead, like ""?
    That would be easier to work with than errors.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISERR with COUNTIFS

    Try
    =SUMPRODUCT(--ISERROR(Dates!F6:F7000),--(Dates!C6:C7000="Car" ))

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: ISERR with COUNTIFS

    =SUMPRODUCT(--ISERROR(Dates!F6:F7000),--(Dates!C6:C7000="Car" ))
    That should work.

    I guess it is just my own personal preference that I really do not like errors showing up on my report (makes people question the validity of the report/data).
    So I would probably use an IFERROR formula in Dates!F6:F7000 to return a blank instead of an error, i.e.
    Code:
    =IFERROR(current formula,"")
    Then, the SUMRPODUCT would look like:
    Code:
    =SUMPRODUCT(--(Dates!F6:F7000=""),--(Dates!C6:C7000="Car" ))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISERR with COUNTIFS

    Thank you both very much, both very useful solutions 😀

Some videos you may like

User Tag List

Tags for this Thread

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
  •