Excel 2007 : counting individual cells in a row

[OpaBollard]

Hi I do the stats for a bowling organisation and i have a spreadsheet set up that I enter the rink scores and then it works out the difference between those scores, and gives points 2 for a win 1 for a draw and 0 for a loss Then at the end of 18 rounds I need to count how many times 2,1 and 0 appear to show How many losses wins and draws they had But I use the countif function but have to select the whole range and it returns wrong. As when there is a draw the points is 0 and the countif counts that as a loss when it was not What I need to do is only count the cells in the row that return the 2,1 or 0 But I just can not fiqure out what to do at all Every thing I try I get errrors


C Hobbs Sandy Bay White
30 8 22 2 16 17 -1 0 21 18 3 2 31 20 11 2 20 16 4 2 27 17 10 2 31 15 16 2 35 12 23 2 23 17 6 2 20 19 1 2 20 18 2 2 19 13 6 2 25 15 10 2 18 18 0 1 21 16 5 2 23 19 4 2 29 122 16 15 2 2 100.0%

That is a copy of 1 row as you can see at the end it shows her having 2 draws and 2 losses when She only had 1 loss and 1 draw So is there a way to count only certain cells in a row Any help will greatly be appreciated
Cheers Opa
ps first number is her score 2nd numer is oponents score Then I have a formular to give the difference in this case 22 shots then I use another formular to see if it was a win ect and this was a win so it returns 2 points As at end of year I add all there points up as well

[Marcol]

Hi OpaBollard
Welcome to the forum ...

Without seeing your workbook it is difficult to say what will work.
Best if you post a sample workbook showing your sheet layout and some typical rows.

However, assuming the sample row you have posted is correct, (there seems to be a few anomalies), and begins in A2.

For Wins

Please Login or Register  to view this content.

For Draws

Please Login or Register  to view this content.

for Losses

Please Login or Register  to view this content.

Does that help?

[OpaBollard]

I could not get that code to work I will attach the file
I did not acttually right the spreadsheet the first stats guy did I just inherited it :-)
Cheers Opa

[Cutter]

You could use:
In CB8: =COUNTIFS($C8:$BV8,2,$C$6:$BV$6,"PTS")
In CC8: =COUNTIFS($C8:$BV8,0,$C$6:$BV$6,"PTS")
In CD8: =COUNTIFS($C8:$BV8,1,$C$6:$BV$6,"PTS")

You can simplify your formulas in:
CA8: =SUM(CB8:CD8)
BX8: =SUM(2*CB8,CD8)
BY8: =SUMIF($E$6:$BU$6,$BU$6,E8:BU8)

[OpaBollard]

Thanks cutter I pasted them in but they just all return a 0 as the number
I am a noob with spreadsheets

[OpaBollard]

I got it to work by adding in after the results table 18 more columns that I used =f8 =j8 ect in then I used the countif function on that range And it seems to work Was all that I and my mate could think of
I just thought there had to be a formular to do it easer :-)

[OpaBollard]

Here is the spreadsheet after I added my bits in
I was also thinking Could I set up a access database To enter the info in and then just produce the reports I need The result forms I recive each week are all the same format We have 10 divisons of 10 teams In men 5 divisions of 10 teams in Wowmen and 4 divisions of 10 Teams in Midweek Would this be possable?
PS So glad I found this site is awsome

[Cutter]

Here is your original submitted file with the formulas I suggested in place on your Annual Report sheet - columns BX to CE - for Div 1. I also changed formulas columns E to BV.
You typically had this:
=IF(BS8>BT8,"2",IF(BS8=BT8,"1",IF(BS8<BT8,"0")))
By having the 2,1 and 0 within quotes you are returning text values instead of numbers so I changed (and simplified) to:
=IF(BU8="","",IF(BU8>0,2,IF(BU8=0,1,0)))
I have added a light grey fill to all cells where I have changed formulas.

As for your latest question about Access - I don't know.

[OpaBollard]

Awsome thanks mate