Count number of times two cells match across whole sheet

New Contributor

I have a list of names and then in each subsequent column, their team colour listed (Red/Blue). Each week a new column with the each person's team colour for that week will be added.

I am trying to write a formula to count up how many times two people have been on the same team, by using a countifs statement and checking the text values (see below). However, without making each formula incredibly long, I don't see how I can cover the whole sheet so that it updates whenever a new column of of colours is added. Any advice?



So far, this check if the cells in B3 (Person 1's colour) and B4 (Person 2's colour) match (Red/Blue), then does the same for D3 and D4 (the next week) but I want this to go on for 20+ weeks without having to type in a separate countif each time.

2 Replies
best response confirmed by Zongear (New Contributor)



You can try this formula.





Sheet with the formula that counts times two cells match: