View original (French)

Excel negation

yur -  
Morgothal Posted messages 1350 Status Member -
Bonjour,

please help me to write a formula in Excel that allows me to count the number of cells that do not contain a given character.

=COUNTIFS(Sheet1!C:C,"GIE")

thank you
Configuration: Windows XP / Safari 536.11

2 answers

  1.
    chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
     
    Hello,

    this formula should correspond to what you want:
    =COUNTA(Sheet1!C:C) -COUNTIF(Sheet1!C:C,"*GIE*")

    That is: the number of non-empty cells - the number of cells containing GIE.

    Best regards.
    --
    This guy wasn't very bright, as he was foolish.
    0
    1. yur
       
      Thank you for your reply :)
      0
    2. yur
       
      =NB.SI.ENS(Feuil1!C:C;"<>SNC";Feuil1!F:F;"INDUSTRIE AGROALIMANTAIRE")
      0
    3. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      The formula should be:
      =COUNTIFS(Sheet1!C:C,"<>"SNC"",Sheet1!F:F,"agri-food industry")
      0
    4. yur
       
      You're welcome :)
      0
  2. Morgothal Posted messages 1350 Status Member 184
     
    Hello,
    Try this one if the cell contains "GIE": 
    =COUNTIF(C:C,"<>*GIE*")
    0
    1. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      Hello Morgothal,

      the formula should work, but in this case, we need to restrict the range of cells. Otherwise, the formula picks all cells that do not contain GIE, including empty cells.

      Best regards.
      0
    2. Morgothal Posted messages 1350 Status Member 184
       
      Ah, that's right.
      0