CountIFS with multiple ranges and multiple criteria

Question

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.

Maybe something like =SUMPRODUCT(--(A1:A4&B1:B4&C1:C4="abb"))? — Foxfire And Burns And Burns, Commented May 13, 2022 at 11:20

JvdV · Accepted Answer · 2022-05-13 11:58:30Z

3

One option:

Formula in E1:

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

answered May 13, 2022 at 11:58

JvdV

76.1k8 gold badges44 silver badges85 bronze badges

Add a comment |

Mayukh Bhattacharya · Accepted Answer · 2022-05-13 13:03:19Z

3

Here is another option,

• Formula used in cell D1

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

answered May 13, 2022 at 13:03

Mayukh Bhattacharya

28k9 gold badges29 silver badges43 bronze badges

Add a comment |

Collectives™ on Stack Overflow

CountIFS with multiple ranges and multiple criteria

2 Answers 2

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Related