Excel find match occurrences between two columns over multiple rows-CodePudding

I have a dataset that has the following columns and I have populated some rows.

I want to find a way that would count the most popular route, i.e the most occurrences of two matching cells across multiple rows. In this example it would be edinburgh and glasgow as that is the route not to show lax even though it appear twice.

Is there a way in excel to do this?

CodePudding user response：

To count routes for each row use:

=COUNTIFS(B:B,B2,C:C,C2)

to get first most popular - use array formula:

=INDEX(B1:B8 &"-" & C1:C8,MATCH(MAX(COUNTIFS(B1:B8,B1:B8,C1:C8,C1:C8)),COUNTIFS(B1:B8,B1:B8,C1:C8,C1:C8),0))

CodePudding user response：

You may try using this formula, which shall give you the number of most occurrences of two matching cells across multiple rows

Formula used in cell D2 --> This shall show the rows of most occurrences

=IF(COUNTIFS($B$2:$B$6,$B$2:$B$6,$C$2:$C$6,$C$2:$C$6)>1,"Most Occurences","")

In the above formula, if you just remove the IF Function it shall give you the counts like the below formula, and always try to use absolute cell references it makes excel to calculate faster

=COUNTIFS($B$2:$B$6,$B$2:$B$6,$C$2:$C$6,$C$2:$C$6)