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).
How this formula works
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.
To count rows in a table that meet internal, calculated criteria, without using a helper column, you can use the SUMPRODUCT function. Context Imagine you have a table of sales figures for several products. You have a column for sales last month and...
To count rows in a table that meet multiple criteria, some of which depends on logical tests that work at the row-level, you can use the SUMPRODUCT function. Context You have a table that contains the results of sports matches. You have four columns...
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.