Please help this novice.

[cupofjoe1962]

I am trying to count the matches of two rows of cell data and put the total in cell S8.

It works on a cell to cell match.
=COUNTIF(B5,B8)

I cannot figure out how to check TWO rows and add the total of
matches in cell S8.



I am looking to match
B5, C5, D5, E5, F5, G5, H5, I5, J5, K5, L5, M5, N5, O5, P5, Q5
against
B8, C8, D8, E8, F8. G8. H8. I8. J8. K8, L8, M8, N8. O8. P8, Q8

Any help will be appreciated.

This is for my "entertainment purpose only" football pool

[BobBridges]

I never got interested in major-league sports until soccer came to the US; that captured my imagination. Go figure. Consequently I don't know much about these pool thingies. But from looking at your sample, I gather you want an easy way to see, probably in a column to the right (perhaps the "Wins" column?) a count of the cells in that row where the subscriber's pick matches the actual winner in row 5. Right?

One of our number, I think it was Pecoflyer, just got me started on array formulae, which I'd never fooled with before. I got that to work by putting this as an array formula in the Wins column (though of course you can put it anywhere you like):

=SUM(($B8:$Q8=$B$5:$Q$5)+0)

I repeat, this has to entered as an array formula; that means you plug this into a cell in column S (or wherever) and hit not <Enter> but <Ctrl-Shift-Enter>. In the formula bar it displays with curly braces around it, to show that you did it that way.

Here's how I think it works:

$B8:$Q8 is of course an array for the values on this row.

$B$5:$Q$5 is, equally of course, the array on the winner's row.

$B8:$Q8=$B$5:$Q$5 is the comparison of the values in each array, to each other, one by one; the result is an array of the same size but consisting of the values True or False.

When you put parens around that and add 0 ("($B8:$Q8=$B$5:$Q$5)+0"), Excel converts all the True and False values to 1 and 0, respectively; so now you have an array of 1s and 0s.

When you SUM the result, the result is a count of True values, ie the number of matches.

Now, next you're going to want to know how to get the sheet to pick out and display the actual winner automatically. But I don't know what you do about point spreads—in fact I'm only vaguely sure what a "point spread" is—so I'll leave that for someone else.

[Pecoflyer]

Maybe I'm a little late for this one.
Bob is on the good track, and XL already provides a solution

try =SUMPRODUCT(--($B8:$Q8=$B$5:$Q$5)) which is an array function in itself and does not need commitment with CSE. The double unarry operator -- will convert TRUE/FALSE to 1/0 combinations

Be aware that the range lengths MUST be the same and defined ( B:B will not work)