CountIFS with multiple ranges and multiple criteria-CodePudding

I want to count all the instances that "a" occurs in column a AND "b" occurs in both column b and c. This would be in a match up style. Essentially I am trying to count a head to head where a occurs in column a every time b occurs in the remaining columns

The formula I used is =COUNTIFS(A1:A4,"=a",B1:C4,"=b"). This returns a "#VALUE" error.

I am aware that I could use multiple COUNTIFS but for the real workbook "b" would be spread across 20 columns so this would result in a very long and slow formula.

CodePudding user response：

One option:

Formula in E1:

=SUM(--(BYROW(A1:C4,LAMBDA(a,CONCAT(UNIQUE(a,1))))="ab"))

CodePudding user response：

Here is another option,

• Formula used in cell D1

=SUMPRODUCT(MMULT(--($A$1:$B$4="a")*($B$1:$C$4="b"),{1;1}))