Count Formulas - Count paired items in a row column combination from a guide

Count paired items in a row column combination from a guide

Syntax of used function(s)

IF(logical test, do something when true, do something where false)
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The IF function allows you to make logical comparisons between a value and what you expect.
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met

What to do?

To make a summary table for a paired items that appears in a row column combination in a tabular form and to counts their occurences by the help of guide column the IF function along with COUNTIFS can be used. The formula shown in H5 column.
We are going to count how often the any two items from column c1, c2, and c3 appears together, such as, AC, ED, BF,.. e.t.c.

Formula

=IF($G5=H$4,"nil",COUNTIFS(Guide,"*"&$G5&"*",Guide,"*"&H$4&"*"))

How this formula works

The COUNTIFS function counts the value of a cell found in the range name "Guide". The "*"&$G5&"*" means "*$G5*", that means, search the value of G5 in the range "Guide", whether it exists or not and count, the other criteria also do like this and returns a count.
For example, in the picture shows, in the cell K6, the value of K4 and G6 combination that means the pair DB appears in the range "Guide" in 3 times such as in E6, E8, and E11. Therefore the value of same pair will not returen any value, it will return 'nil' by the use of IF function. No repeation of value in the range "Guide", i.e. AA, BB,...e.t.c.

Previous: Excel Formulas - Count numbers beginning with a specific number
Next: Excel Formulas - Count long number of more than 15 digits not using COUNTIF()