Counting Particular Cells

[LS Flowers]

I posted on 11/20/08 asking for a formula to do a Win/Loss tally.
The response worked greatly.
I am now attempting to do the same type tally except I want a tally for "in conference" Wins/Losses.
I tried the same "SUMPRODUCT" formula as before except this time I held the control key down so as to use only the cells desired. I'm getting "VALUE" for my answer though.
The row I have added in for in conference tally is row 3.
All blue colored cells are for the ACC conference and the green are for the SEC.
Is there a way to get the win/loss figures for selected cells / rows only?
Attachment added below.
Thank you

[martindwilson]

cant you just use sum in the format =sum(a1,z23,aa46,ax500) or does it have to work out which are conference (coz i cant!). what should the totals so far be?
edit
ok i see you want the wins /loses only totals not points score,
how do you know which are "conference" ?

[LS Flowers]

Thank you for your response.
I color coded the conferences light blue for ACC and light green for SEC.
Sorry I wasn't more clear but I made the sheet for my use and I already knew the conference teams.
Therefore for the ACC teams, I simply used the same formula as for the w/L in row five with the exception of clicking only on the cells for the conference.
For whatever reason, I can't get the same sumproduct formula to work in the conference row as did in the overall row.

[martindwilson]

well i found a bit of vba that makes non ajacent cells into array, i've excel 97 this is for 2000< but it
seems to work ok
http://support.microsoft.com/kb/213403
i used it in the format
=SUMPRODUCT(--(makearray(A1,A3,A5,A7)>makearray(B1,B3,B5,B7))) without doing anything else no ctrl shift enter or anything
and it counted exactly as expected

[LS Flowers]

Thank you martindwilson.
Looks like that will work just fine and I sure do appreciate your help.
Thanks again and have a wonderful Thanksgiving and a Merry Christmas.

[LS Flowers]

Looks like I got a little ahead of myself here.
I'm going to need someone to lead me by the hand thru this.
Thanks for the help

[martindwilson]

put the code into a module just follow microsofts instructions
Start the Visual Basic Editor (Press ALT+F11)
On the Insert menu, click Module.
Type(copy/paste) the sample Visual Basic MakeArray function code (shown above) into the module.(thats this bit )

Please Login or Register  to view this content.

Return to the worksheet (Press ALT+F11)

once thats in the sheet you use "makearray" in formula

=SUMPRODUCT(--(makearray(A1,A3,A5,A7)>makearray(B1,B3,B5,B7)))

wher a1,a3,a5,a7 and so on correspond to the "conference" home cells and
b1,b3,b5,b7 corespond to the adjacent conference away cells

[LS Flowers]

Thanks again but I'm not having any luck.
I am using XL 2000 if it matters.
Also, some of the cells included in the formula are right on top/bottom of each other if that even matters.

I'm receiving an error code telling me to press "alt F11" but I have done that.
Over and over but to no avail.

I will keep trying.

Thanks anyway.

[LS Flowers]

BINGO--BINGO--BINGO !!!
It's working now.
I was trying to introduce the macro to the cell instead of the page.
Please forgive my ignorance since I've earned that status well.
Have a wonderful Thanksgiving and a Merry Christmas.

PS, I forgot how to post "SOLVED" to the post.