2

In this example I have tried to find matches between 1 column and 3 columns. It works. Now I want to count the hits BUT if for some reason there is an odd value it still counts.

enter image description here

I have tried:

=COUNTUNIQUE(ARRAYFORMULA(MATCH(E3:E;{A$3:A;B$3:B;C$3:C};0)))

The desired output would be a total count of 3 (Apple, Tomato, Pear) and still 3 missing (Banana, Squash, Cucumber) and unique count = 3 and not 4.

What will I have to change?

player0
  • 124,011
  • 12
  • 67
  • 124
Allan Bech
  • 391
  • 1
  • 6

1 Answers1

1

G3:

=COUNTA(IFERROR(FILTER({A3:A;B3:B;C3:C}; 
 REGEXMATCH({A3:A;B3:B;C3:C}; TEXTJOIN("|"; 1; E3:E)))))

G6:

=COUNTA(E3:E)-COUNTA(IFERROR(FILTER({A3:A;B3:B;C3:C}; 
 REGEXMATCH({A3:A;B3:B;C3:C}; TEXTJOIN("|"; 1; E3:E)))))

G11:

=COUNTA(IFERROR(UNIQUE(FILTER({A3:A;B3:B;C3:C}; 
 REGEXMATCH(LOWER({A3:A;B3:B;C3:C}); LOWER(TEXTJOIN("|"; 1; E3:E)))))))

note LOWER() for case insensitivity

player0
  • 124,011
  • 12
  • 67
  • 124
  • If you don't mind me getting back to this question, please take another look at the formular you provided earlier today: =COUNTA(IFERROR(UNIQUE(FILTER({A3:A;B3:B;C3:C}; REGEXMATCH({A3:A;B3:B;C3:C}; TEXTJOIN("|"; 1; E3:E)))))) It turns out, as always, in real life, users can't be trusted to write excactly. So how do I change the | to (?i) case insensitive? – Allan Bech Mar 20 '20 at 19:52