Need help computing win/loss statistics

[Rymbeld]

Ok, so here is the problem. Given a sheet that has columns named "opponent; date; side played; result" and rows which give a name, date, etc, like this:

Name	Date	Side Played	Result
Bob	xyz	White	Win
Tim	xyz	Black	Loss
Sally	xyz	White	Win

I want to generate two more sets of data on a separate sheet. I'm such a chess fanatic that I want to keep track of how many games I've played against each opponent, and my win-loss stats against each; I also want to quickly compute my win percentage as each side.

Therefore I'd have one set of data that would have rows that read:

Opponent	Games Played	Wins	Losses
Bob	1	1	0

Getting Opponent and Games Played is easy enough. I simply Count the number of times "Bob" shows up in the Name column from the previous sheet. But what I also want to do is count the number of times "Win" comes up, but ONLY IF "Bob" occurs in the same row. How on earth do I do that?

Figuring out win percentages is a similar problem, really. But none of the tutorials I've found online approach dealing with things this involved.

Thanks in advance.

[FDibbins]

try using countif()

you're profile says you are using 2003? is this correct, because if not (2007+) then you could also consider using countifS()

[Rymbeld]

I'm actually using Google Spreadsheet. I don't at all see how to get countif to do what I want...I use countif to populate "Game Played", but I can't use it to populate Wins... I've tried "=countif() AND countif()" but that returns a syntax error. CountifS looks like if would work but it is not in Google spreadsheets. I've been puzzling on this problem for a while now. Hm...

[Rymbeld]

Am I going to need to learn database functions for this?

[FDibbins]

no you dont lol. the syntax for countif() is

=countif(the-range-you-want-to-count,criteria) (which im sure you already knew)

try this...add a helper column and combine the name and the win. if name is in A and result is in D then in E? use =A1&D1

then you're countif would be =countif(E:E,"Bob"&"Win") you could put the name and result intheir own cell and then reference them, or just use "BobWin"

if this doesant work for you, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.