Counting pairs in a table, excel

user_16435751 0
Viewed 43

I have a table consisting of character names in excel

Small section of table:
enter image description here

I'm looking to count the number of times a pair occurs in a row. eg the number of times yasuo and atroxx appear (3) or the amount of times zoe and zed appear (2) I've tried countifs() but that doesn't work and am looking for any suggestions

excel
2 Answers

For long table it would be easier to solve the issue if you have Excel-365. Try below formula with Excel-365.

=SUM(MMULT(--(A2:E7="aatrox"),SEQUENCE(COLUMNS(A2:E2),,,0))*(MMULT(--(A2:E7="yasuo"),SEQUENCE(COLUMNS(A2:E2),,,0))))

For older versions of excel try-

=SUM(MMULT(--(A2:E7="aatrox"),TRANSPOSE({1,1,1,1,1}))*(MMULT(--(A2:E7="yasuo"),TRANSPOSE({1,1,1,1,1}))))

enter image description here

user_55147470

I think you can transform this question to 3 steps:

  1. you count/countif wheather the name in each row;
  2. you calculate weather the numbers of each target name appears are bigger than 1;
  3. finally, sum the 0/1 result and you get the answer;

one demo

user_162384740
Related