COUNTIF with changing criteria

[Hamwaggle]

I feel like I am having a complete moronic excel moment. I usually am the KING of excel but this is really starting to **** me off. Here is what I am trying to do. For the sake of the confidentiality of my work I will use an example. Let's say there is a spreadsheet of 5 people who attempt to predict the outcome of the football games each week.

Billy.........John........Steve........Etc..........WINNER (OUTCOME)
Colts
Bears
Lions
Saints
Eagles
Falcons

Billy's picks are above, the others would be next to his in separate columns but the same rows.

After the games are over there is another column next to the last person in which the winner of the game is typed in.

I would like to use a count if formula that counts the number of correct picks a person made, so I need a formula where if cell A2 (billy's colts pick) = let's say F2 (where the winner team name is typed exactly how it was entered by the people), it would be counted. The ended number in the formula would be the number of times that A2=F2, A3=F3, A4=F4, etc, etc. That formula would obviously be copied over for each person so the number of game guessed correctly would be displayed in one formula using count it.

I know I could accomplish this by doing something like =SUM(IF(A2=F2, 1, 0), IF(A3=F3, 1, 0), ......ect)

Where if the persons guess is equal to the result in row 2 I assign a 1 to it and then sum all of the rows with the same if statement to get a total number of correct guesses.

If this is too confusing please let me know and I will create an example spread sheet, but it's fairly straight forward what I want to do. I just want one single formula that counts the number of picks where it is equal to the results. Keep in mind the result can be Team 1, Team 2, or PUSH.

Is this incredibly easy like I think it is, or can this just not be done with a count if and I need to use my lame, uninteresting formula?

Thanks

[rundie_d]

Hi

Try:

=sumproduct(--(A2:A7=F2:F7))

Rundie