Count paired values within a single row or column

Hello,

I want a formula for counting paired values (numeric or non-numeric) within a single row or column, irrespective of order. Searching for an answer has only given me COUNTIF formulae for counting pairs between two or more columns, but I feel there should be some kind of an array solution...?

In the example below, each Pair (A, B, or C) in the first row corresponds to one of two values in the Choice row (A = 2 or 3, B = 4 or 5, C = 6 or 7).

Within the Choice row, I want to count the total number of times, for each value pair, both "choices" were selected. Another way of putting this, in terms of this example, would be "How many instances of the value 2 have a corresponding value 3 within the same row?".

Finally, I want to sum these totals.

Excel count question.png

Hence, the answer to the above example is:
Pair A = 2 (the value 2 appears 6 times, but has a corresponding value 3 only twice).
Pair B = 2 (the value 4 appears 5 times, but has a corresponding value 5 only twice).
Pair C = 4 (the value 6 appears 4 times and each instance has a corresponding value 7).
Total = 8.

Hope that all makes sense and someone can help. Thanks very much!

Just set up criteria range.

Ex: in AA3:AA5, A, B, C
In AB3:AB5, 2, 4, 6
In AC3:AC5, 3, 5, 7.

Then in AD3:

Formula:

Please Login or Register  to view this content.

Copy down.
In AD6: SUM(AD3:AD5)

What's considered "Pair" and where is that definition stored?

If you have access to PowerPivot (Data model and DAX) I'd perform calculation using DAX measure.

Alternately you could add helper column.

But then, I know nothing of your workbook set up. If you need more specific help, I'd recommend you upload sample workbook that mirror your actual workbook set up, with desensitized info.

Oh, as FYI this could also be done using PowerQuery Add-in.

In both PowerPivot & PowerQuery, data should be transposed to Flat table structure (Pair, Choice as header row, data growing length wise).

Off top of my head, can't think of dynamic formula solution for this.

Perhaps this will be of help.
Z1:AB1 are populated with the 'pair' identifiers A, B and C respectively.
Z2:AB2 are populated using: =MIN(COUNTIFS($B2:$X2,INDEX($B2:$X2,MATCH(Z1,$B1:$X1,0))),COUNTIFS($B1:$X1,Z1,$B2:$X2,"<>"&INDEX($B2:$X2,MATCH(Z1,$B1:$X1,0))))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. If you should find the solution works with the other examples, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.