SOLVED

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?

=COUNTIFS(Sheet2!$B$3,"Red",Sheet2!B4,"Red")+COUNTIFS(Sheet2!$B$3,"Blue",Sheet2!B4,"Blue")+COUNTIFS(Sheet2!$D$3,"Red",Sheet2!D4,"Red")+COUNTIFS(Sheet2!$D$3,"Blue",Sheet2!D4,"Blue")+COUNTIFS(Sheet2!$F$3,"Red",Sheet2!F4,"Red")+COUNTIFS(Sheet2!$F$3,"Blue",Sheet2!F4,"Blue")...........

 

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)
Solution

@Zongear 

=SUMPRODUCT(ISEVEN(COLUMN(sheet2!A:AA))*(((sheet2!A3:AA3="Red")*(sheet2!A4:AA4="Red"))+((sheet2!A3:AA3="Blue")*(sheet2!A4:AA4="Blue"))))

You can try this formula.

 

Sheet2:

sheet2.JPG

 

Sheet with the formula that counts times two cells match:

sumproduct.JPG