comparing, coupling, and counting multiple cells

[Kevuas]

I have data in the following format:

A1 : @ANA
A2 : W

B1 : @TEX
B2 : L

C1 : TEX
C2 : W

etc etc (many many more)

These represent a list of opponents and outcomes (a win = "W", loss = "L"). Here is what I would like to do: I would like to output a tally of all the AWAY games that resulted in wins. In the case mentioned above, I would like the value to be "1", which corresponds to the data in A1, A2. I can't figure out how to get the value to return for the @TEAM coupled with the outcome W.

[Kevuas]

I just thought of something that may or may not be useful. I think I might be able to use the LEN function, given that all the "away" games will be of LEN=4, while all the "home" games are LEN=3. This might help avoid needless use of the wild-card function to identify the "@".

Any thought from anyone would be greatly appreciated on this, as I am stumped!

[eliW]

On 2002-04-20 20:58, Kevuas wrote:
I have data in the following format:

A1 : @ANA
A2 : W

B1 : @TEX
B2 : L

C1 : TEX
C2 : W

etc etc (many many more)

These represent a list of opponents and outcomes (a win = "W", loss = "L"). Here is what I would like to do: I would like to output a tally of all the AWAY games that resulted in wins. In the case mentioned above, I would like the value to be "1", which corresponds to the data in A1, A2. I can't figure out how to get the value to return for the @TEAM coupled with the outcome W.

Hi Kevuas,
I assume that you have your reasons for positioning your data like you did, but putting it in more reasonable way would made it much more easy to sum and handle by using Pivot tables or Subtotals or even macro's.

Eli

[Tom Urtis]

Try this array formula (Ctrl+Shift+Enter):
=SUM(ISNUMBER(SEARCH("@",A1:C1))*(A2:C2="W"))

[Kevuas]

The array formula worked. Thanks very much Tom. To the other person who commented about my troublesome data organization, you are probably right... I am not an expert on here. If you could suggest a better way to organize it, thanks!

The formula worked great!!!

[eliW]

On 2002-04-21 07:25, Kevuas wrote:

The array formula worked. Thanks very much Tom. To the other person who commented about my troublesome data organization, you are probably right... I am not an expert on here. If you could suggest a better way to organize it, thanks!

The formula worked great!!!

hi Kevuas,
I would handle it like this:
-A--------B-------C--------D-------E---
name----H-win---H-loss---A-win---A-loss
TEX----------------1-------------------
ANA------1-----------------------------
TEX-------------------------1----------

Regards
Eli