Forum Discussion

drubetskiyae's avatar
drubetskiyae
Copper Contributor
Sep 12, 2024

COUNTIFS function

When trying to count the number of cells containing one and the second value, the function returns 0

 

a
a
a
b
b
c
c
c

=COUNTIFS(A1:A8;"a";A1:A8;"b")

  • drubetskiyae 

    With formula =COUNTIFS(A1:A8;"a";A1:A8;"b") you count the number of cells which contains "a" AND "b". No such.

    In addition to Martin_Angosto variants are

     

     

    =SUM( (A1:A8="a") + (A1:A8="b") )

     

     

    if you are on 365 or 2021; or

     

     

    =SUMPRODUCT( (A1:A8="a") + (A1:A8="b") )

     

     

    for previous versions.

    • drubetskiyae's avatar
      drubetskiyae
      Copper Contributor

      SergeiBaklan Thanks for the answer! It is very strange that this function does not work for such obvious conditions. Although it works when the condition AND is specified but with the condition NOT EQUAL🤔

      For example:

      a
      a
      a
      b
      b
      c
      c
      c
      d
      d
      d
      e
      e
      e

      5               

       

      =COUNTIFS(A1:A14;"<>c";A1:A14;"<>d";A1:A14;"<>e")

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        drubetskiyae 

        As SergeiBaklan explained:

        A cell cannot be equal to "a" and equal to "b" at the same time, so =COUNTIFS(A1:A14; "a"; A1:A14; "b") will always return 0, regardless of what A1:A14 contains.

        But a cell can be unequal to "a" and unequal to "b" at the same time, for example if it contains "c" or "d". So =COUNTIFS(A1:A14; "<>a"; A1:A14; "<>b") can return a positive number.

Resources