Using Wildcard with Countifs with Search Criteria in Cell

Question

I have a set of data cells B1:C5 with Tests that are a mix of Alphanumeric characters along with data next to it. I am trying to create a formula for cells B8:C10 that will parse through the data in A2:C5 and, using the CountIfS function, count the number of cells that match the specimen and value 1. The problem occurs with some of the data whose test names include an average of multiple specimens. I wish for each specimen in those tests to be counted. Here is a visualization of the excel sheet:

Input
+----+------------+---------+---------+
|    |     A      |    B    |    C    |
+----+------------+---------+---------+
|  1 | Test       | Value 1 | Value 2 |
|  2 | 1          | TRUE    | ...     |
|  3 | 14A        | FALSE   | ...     |
|  4 | 1, 14A Avg | FALSE   | ...     |
|  5 | 1, 53 Avg  | TRUE    | ...     |
+----+------------+---------+---------+

Current Output
+----+------------+---------+---------+
|    |     A      |    B    |    C    |
+----+------------+---------+---------+
|  7 | Specimen   | TRUE    | FALSE   |
|  8 | 1          | 1       | 2       |
|  9 | 14A        | 0       | 2       |
| 10 | 53         | 1       | 0       |
+----+------------+---------+---------+

Desired Output
+----+------------+---------+---------+
|    |     A      |    B    |    C    |
+----+------------+---------+---------+
|  7 | Specimen   | TRUE    | FALSE   |
|  8 | 1          | 2       | 1       |
|  9 | 14A        | 0       | 2       |
| 10 | 53         | 1       | 0       |
+----+------------+---------+---------+

I have been using the following formula in cell B8: (ignoring $ for ease of reading)

=COUNTIFS(A1:A5, "*" & A8 & "*", B1:B5, B7)

The problem I am running into occurs from how the formula is evaluating Specimen 1; it is not counting Test 1 in Row 2 and it is counting Test 14A in Row 3. I have found a solution which involves placing a comma at the end of each specimen in the Avg tests and using the following formula:

=COUNTIFS(A2:A5, A8, B2:B5, B7) + COUNTIFS(A2:A5, "*" & A8 & ",*", B2:B5, B7)

The problem with using this method is that I have to change the names of the Avg Tests (a solution I would like to avoid) and with all the other Values I'm going to be evaluating, it will make the formula very long and unwieldy. I'm wondering if there is a simpler way to combine the two CountIfS above into one without manipulating the names of the Tests or Specimens.

Scott Craner Scott Craner · Accepted Answer · 2017-03-13T17:03:23

You will need to use SUMPRODUCT() and some other formula to parse the data:

=SUMPRODUCT((ISNUMBER(SEARCH(" " & $A8 & " "," " & SUBSTITUTE($A$2:$A$5,","," ") & " ")))*($B$2:$B$5=B$7))

By changing the , to spaces and adding spaces to the front and back of the strings, we can also bracket the search value with spaces to ensure that we are looking for the full entry and not part.

Then the SUMPRODUCT counts each time the complete value is found in each string and the Value one is equal to the header.

Using Wildcard with Countifs with Search Criteria in Cell

1 Answers