Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE! because
the two ranges will be different sizes until you make entries in both. You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from this
group....
I am also using conditional formatting from B4:AZ65, which highlights the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so on),
each time I start a new col.otherwise it fails to enter the number of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry