Excel using COUNTIFS and OR-CodePudding

In my example the result should be 3 but it shows me 0. Count, if in

A:A = 1 AND B:B = (3 or 4 or 5).

I was trying something like that but it doesnt work:

=COUNTIFS(A:A; 1; B:B; OR(B=3;B=4;B=5))

------------------------ EDIT:

Let's say I have one more colum with characters:

And I want to count only the character with "a".

CodePudding user response：

Use formula like shown below, since commented above:

• Formula used in cell E2

=SUM(COUNTIFS(A:A,1,B:B,{3,4,5}))

Or,

• try using SUMPRODUCT()

SUMPRODUCT(COUNTIFS(A:A,1,B:B,{3,4,5}))

After OP has edited post:

Alternative versions & required output shown below:

• Formula used in cell F2

=SUM(COUNTIFS(A:A,1,B:B,{3,4,5},C:C,"a"))

• In German Version:

=SUMME(ZÄHLENWENNS(A:A;1;B:B;{3.4.5};C:C;"a"))

• Formula used in cell G2

=SUM((A2:A10=1)*(C2:C10="a")*(B2:B10={3,4,5}))

• In German Version:

=SUMME((A2:A10=1)*(C2:C10="a")*(B2:B10={3.4.5}))

• Formula used in cell H2

=SUM(MMULT(--((A2:A10=1)*(C2:C10="a")*(B2:B10={3,4,5})),{1;1;1}))

• In German Version:

=SUMME(MMULT(--((A2:A10=1)*(C2:C10="a")*(B2:B10={3.4.5}));{1;1;1}))

CodePudding user response：

You can use this formula: =SUM(COUNTIFS(A2:A10,1,B2:B10,{3,4,5}))

But I would recommend to "outsource" the criteria for 3,4,5 to an extra range. Then it is much easier to update the filter if necessary.