View original (French)

NB.IF.DIFFERENT DE

Solved
nicolas4107 Posted messages 3 Status Member -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,
I have a problem with the Excel formula COUNTIFS.
I want to do what is different from 20-12, so I write the following value:
....;"<>20-12")
However, Excel interprets this as anything different from 8. (20 - 12)
Does anyone have a solution?

1 answer

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    if you write 20-12, it's no surprise that Excel takes that for an 8; there are plenty of mathematicians who have done that before him!
    if you want to count the values different from 8 or 20 in the range A1:A100

    =COUNTIFS(A1:A100,"<>12",A1:A100,"<>20")
    and if your range contains empty cells that you do not want to count:
    =COUNTIFS(A1:A100,"<>12",A1:A100,"<>20",A1:A100,"<>")

    But in the end, if it's a text that you want to exclude:

    =COUNTIF(A1:A100,"<>20-12")

    kind regards

    To err is human, to persist is diabolical
    9
    1. nicolas4107 Posted messages 3 Status Member
       
      Hello Vaucluse, and thank you for your reply.
      It is indeed a text that I want to exclude.
      I just tried "<>"&20-12, but Excel still considers it an 8!
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Exactly, I must have missed something in my essay.
      I've never dealt with it like this!!!??
      But strangely, if this time I didn't make a mistake, the formula correctly counts the number of 20-12 in the list with:

      COUNTIF(A1:A10,"20-12") and doesn't confuse them with a value of 8

      So we can try:
      =COUNTA(A1:A100)-COUNTIF(A1:A100,"20-12")

      Or more simply:
      =SUMPRODUCT((A1:A100<>"20-12")*1)

      best regards
      2
      1. nicolas4107 Posted messages 3 Status Member > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         
        I just used the following method that works; in my opinion, it's not the best solution, there must be something simpler, but at least it has the merit of working!
        =COUNTA(A1:A100)-COUNTIF(A1:A100,"20-12")
        This solved my problem, thank you very much!
        0
      2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453 > nicolas4107 Posted messages 3 Status Member
         
        OK, I consider the topic resolved
        .. but for me SUMPRODUCT looks cleaner
        Safe travels
        best regards
        0