To build a summary table with a count of paired items that appear in a list of existing combinations, you can use a helper column and a formula based on the COUNTIFS function. In the example shown the formula in cell H5 is:
Note: this formula assumes items don't repeat in a given combination (i.e. AAB, EFE are not valid combinations).
We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we try to add criteria looking for 2 items across 3 columns, it isn't going to work.
A simple workaround is to join all items together in a single cell, 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 like this:
COUNTIFS is configured to count "pairs" of items. Only when the item in column G and the corresponding item from row 4 appear together in a cell is the pair counted. A wildcard (*) is concatenated to both sides of the item to ensure a match will be counted no matter where it appears in the cell.
SUMPRODUCT is designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. As a result, you can use SUMPRODUCT to process arrays that result from criteria being applied to a range of cells...
The SUMPRODUCT function is programmed to handle arrays natively, without requiring Control Shift Enter. It's default behavior is to multiply corresponding elements in one or more arrays together, then sum the products. When given a single array, it...
The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,...
The Excel CONCAT function concatenates (joins) values supplied as references or constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT allows you to supply a range of cells to join, in addition to individual cell references.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.