How to condense SUM() -- The specified formula cannot be entered...

First post on any forum, ever. Please cut me some slack if this message isn't pretty.
My SUM function is too big and excel doesn't like it.
I have a table of integers with sales reps as column headings and sold products as rows...
In each row (B through N) the highest non-zero value(s -if a tie) are highlighted. //yay for that sales rep
What I am trying to achieve is a count of each highlighted cell from row5 to row67 and present that value in the 68th row of that column without using helper cells.
Can someone help me to find a more elegant function to display the number of times an individual sales rep has been a winner?

Please Login or Register  to view this content.

excel.jpg

I don't know of a formula to work a colour count, but I tried the following and it worked.

Press Alt F11 to get into the VBA editor, then select insert a module (insert menu) into your workbook.

Into this paste the following

Function colornumber(myvar As Range)
colornumber = myvar.Interior.ColorIndex
End Function

Function colorcount(myvar As Range, ColVar As Long)
For Each cell In myvar
If cell.Interior.ColorIndex = ColVar Then colorcount = colorcount + 1
Next

End Function

Once complete, simply click back onto your workbook and enter the following

=colorcount(A1:A67,colornumber($A$1))

You will need to specify cell a1 with the colour you're looking to count.

Hope this helps. If I have misunderstood, my apologies.

Rob

Now that is the elegant solution that I wanted and would have never found by myself.
Never again will I waste hours banging my head on the desk when an expert can solve it in mere minutes.
Thank you daddylonglegs and excelforum!