COUNTIFS function

[drubetskiyae]

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")

[#2]

@drubetskiyae 

A couple of alternatives:

=COUNTIF(A1:A8,"a")+COUNTIF(A1:A8,"b")

=SUM(COUNTIFS(A1:A8,{"a","b"}))

[#3]

@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.

[#4]

@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")

[#5]

@drubetskiyae 

These are bit different things. COUNTIFS works with AND on conditions, i.e. counts values for which condition1=TRUE AND condition2=TRUE, etc

Condition itself could be any one - equal, not equal, equal or greater than, etc.

To apply OR on conditions we have to sum results for each of them.

[#6]

@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.

[#7]

Thank you!

[#8]

@drubetskiyae , you are welcome