Explanation

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 take a stab at add criteria looking for 2 items across 3 columns, it isn’t going to work.

Excel formula: Count if row meets internal criteria

A simple workaround is to join all items together in a single cell in ahelper column, then use COUNTIFS with awildcardto count items.

We do that with ahelper column(E) that joins items in columns B, C, and D using theCONCAT function.

The formula in E5, copied down, is:

As an alternative, you’ve got the option to alsomanually concatenatethe values like this:

Because repeated items are not allowed in a combination, the first part of the formula excludes matching items.

Excel formula: Count if row meets multiple criteria

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, theCOUNTIFS functionis 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 (*)wildcardisconcatenatedto both sides of the value to ensure a match will be counted no matter where it appears in the cell.

Excel COUNTIFS function

Note the references to G5 and H4 aremixed referencesin order to lock the column and row as needed when the formula is copied across the table.

Count if row meets internal criteria

Count if row meets multiple criteria

COUNTIFS Function

The Excel COUNTIFS function returns the count of cells in a range that meet one or more conditions.

Each condition is provided with a separaterangeandcriteria, and all conditions must be TRUE for a cell to be included in the count.

Excel CONCAT function

COUNTIF can be used to count cells that…

CONCAT Function

The Excel CONCAT function concatenates (joins) values supplied as references or constants.

Unlike theCONCATENATE function(which CONCAT replaces), CONCAT will accept arangeof cells to join, in addition to individual…