0

I am trying to filter data in an Excel sheet using functions based on multiple columns. For example, if column B has '*ABC*', I want to count the number of unique values in column A.

 A     B
---   ---
 1    xyz
 1    abc
 2    ABCD
 3    AB
 4    ABCE
 4    qwe
 4    ABC
 5    xyzABC

For the above example, I am expecting the answer 3, since the number of unique values in A matching '*ABC*' are 2,4 and 5 => 3 unique values.

Index Match only returns the first result and I am not able to figure out how to use Countifs to join both these conditions - unique and wildcard match.

dreadnought303
  • 147
  • 3
  • 12

1 Answers1

1

As an Array Formula:

=SUM(IF(ISNUMBER(SEARCH("ABC",B1:B8)),1/COUNTIFS(A1:A8,A1:A8,B1:B8,"*ABC*")))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Fantastic! There is a closing parentheses missing on the right though. Just so I can understand this clearly, am I not supposed to put the wildcards in the search function? – dreadnought303 Jun 17 '17 at 16:08
  • 1
    No the search look for those letters in a string and if found returns the starting position. – Scott Craner Jun 17 '17 at 17:28