Forum Discussion
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")
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.
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")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.
A couple of alternatives:
=COUNTIF(A1:A8,"a")+COUNTIF(A1:A8,"b")
=SUM(COUNTIFS(A1:A8,{"a","b"}))
