1

I'm trying to find the count of unique rows that meet multiple criteria in sheets. Data is like this:

ID |TYPE
1  |T1;T2;T3
2  |T1;T7
3  |T2;T3
4  |T6

I want a count of IDs where type is either T1 or T2. The right answer is 3 (ids 1,2 and 3 have either target type)

=countuniqueifs(A:A,B:B,{"*t1*","*t2*"})

gives me an answer of 2.

Any help greatly apprciated.

player0
  • 124,011
  • 12
  • 67
  • 124
Chris Rosendin
  • 305
  • 2
  • 14

1 Answers1

4

try:

=COUNTUNIQUE(IFNA(FILTER(A2:A, REGEXMATCH(B2:B, "T1|T2"))))

enter image description here

to avoid T11 count in when the criterion is T1:

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Very creative combination of formulas. The only issue is that the regex will match something like "T11". I think it should be change to `=COUNTUNIQUE(FILTER(A2:A, REGEXMATCH(B2:B&";", "T1;|T2;")))` – gries Mar 31 '20 at 01:42