where helper is the named range E5:E16. Note: this formula assumes items don’t repeat in a given combination (i.e. AAB, EFE are not valid combinations). A simple workaround is to join all items together in a single cell in a helper column, then use COUNTIFS with a wildcard to count items. We do that with a helper column (E) that joins items in columns B, C, and D using the CONCAT function. The formula in E5, copied down, is: As an alternative, you can also manually concatenate the values like this: Because repeated items are not allowed in a combination, the first part of the formula excludes matching items. If the two items are the same, the formula returns a hyphen or dash as text: If items are different, a COUNTIFS function is run: Here, the COUNTIFS function is configured to count “pairs” of items. Only when corresponding values from column G and row 4 appear together in the helper column is the pair counted. Because a letter may appear anywhere, the asterisk (*) wildcard is concatenated to both sides of the value to ensure a match will be counted no matter where it appears in the cell. Note the references to G5 and H4 are mixed references in order to lock the column and row as needed when the formula is copied across the table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
