Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Countifs where a column doe not equal x

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

    Default Countifs where a column doe not equal x

    Hi All I have read and read and tried and tried but cant get formula to work.

    Basically the below is the written formula

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>Voided")

    It all works but I need to exclude from the count Anywhere in column DATA!J:J where the entry is Voided or Possible Void

    The formula when you remove that last criteria works and returns the correct number in the count but when I add the last criteria it returns 0 which is incorrect.

    Please help

  2. #2
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Have you tried like this

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>" & "Voided")
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Does this...

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>*Void*")

    succeed as intented?
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,955
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by magpie2000k View Post
    Hi All I have read and read and tried and tried but cant get formula to work.

    Basically the below is the written formula

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>Voided")

    It all works but I need to exclude from the count Anywhere in column DATA!J:J where the entry is Voided or Possible Void
    Misread your request. I think the wildcard like aladin proposed is what you need
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by Aladin Akyurek View Post
    Does this...

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>*Void*")

    succeed as intented?
    Hi No it returns Zeros in every instance. which is clearly wrong

    It is doing my head in as I cant understand why

  6. #6
    Board Regular
    Join Date
    Sep 2013
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by Momentman View Post
    Have you tried like this

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>" & "Voided")
    Both options return a column full of zeros

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

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by magpie2000k View Post
    Both options return a column full of zeros
    The below is a sample of the data in Column J:J

    Quote Created
    Sale Completed
    New
    Quote Created
    Quote Created
    Quote Created
    Sale Completed
    New
    Possible Void
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Live
    Possible Void
    Quote Created
    Quote Created
    New
    Quote Created
    Live
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Sale Completed
    Possible Void
    Quote Created
    Quote Created
    Live
    Quote Created
    Quote Created
    Find Product
    New
    Sale Completed
    Live
    Quote Created
    Quote Created
    Live
    Possible Void
    Sale Completed
    Sale Completed
    Live
    New
    New
    Sale Completed
    Quote Created
    Quote Created
    New
    Quote Created
    New
    Live
    Possible Void
    Quote Created
    New
    Quote Created
    New
    Quote Cancelled
    Quote Created
    Quote Created
    Sale Completed
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Quote Created
    Possible Void
    New
    Voided
    Sale Completed
    Possible Void
    Possible Void
    Live
    New
    Live
    Quote Created

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by magpie2000k View Post
    Hi No it returns Zeros in every instance. which is clearly wrong

    It is doing my head in as I cant understand why
    What is the result of:

    =COUNTIF(Data!J:J,"<>*Void*")
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Sep 2013
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by Aladin Akyurek View Post
    What is the result of:

    =COUNTIF(Data!J:J,"<>*Void*")
    If I go down to row 74 =COUNTIF(Data!J2:J74,"<>*Void*")

    it returns 66 which is correct

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Countifs where a column doe not equal x

    Quote Originally Posted by magpie2000k View Post
    If I go down to row 74 =COUNTIF(Data!J2:J74,"<>*Void*")

    it returns 66 which is correct
    This result might mean that the other one or more conditions (in red) do not hold or incorrectly specified by you...

    =COUNTIFS(Data!$H:$H,'All Dates'!$B3,Data!$I:$I,'All Dates'!$C$2,Data!$F:$F,'Month select 1'!C16,Data!J:J,"<>*Void*")
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •