Quick, clean, and to the point

Count paired items in listed combinations

Excel formula: Count paired items in listed combinations
Generic formula 

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:


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).


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 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

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.