'CountIFS with multiple ranges and multiple criteria

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.

Solution 1:^[1]

One option:

Formula in E1:

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

Solution 2:^[2]

Here is another option,

• Formula used in cell D1

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	JvdV
Solution 2	Mayukh Bhattacharya

'CountIFS with multiple ranges and multiple criteria

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]