Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: comparing, coupling, and counting multiple cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    UCONN
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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!!!

  6. #6
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •