Results 1 to 10 of 10

Thread: Countifs / sumproduct...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2010
    Location
    Manchester, UK
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs / sumproduct...

    I think I am looking for a relatively simple countif output, but stuggling to type it up...

    Basically I've got a load of football results in neighbouring columns: Home score | Away score | Home score | Away score | Home score | Away score | etc... (B6 | C6 | D6 | E6 etc...)

    I want to report in a column at the end the number of times the Home Score is Greater than the Away Score (and Vice Versa)...

    My Header Row (A5:D5 above the scores) are numbered alternating 5 | 6 | 5 | 6..

    So I'm trying to input a formula which looks to see if the values in the columns headed 5 are greater than their neighbours, headed 6...

    Does this need an Array Formula - something like this??

    {COUNTIF(A6:D6,((A5:D5=5)>(A5:D5=6)))} ???

    JP

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,275
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by poolio View Post
    My Header Row (A5:D5 above the scores) are numbered alternating 5 | 6 | 5 | 6..

    So I'm trying to input a formula which looks to see if the values in the columns headed 5 are greater than their neighbours, headed 6...
    Maybe...
    =SUMPRODUCT(--(A6:C6>B6:D6),--(A5:C5=5))

    M.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs / sumproduct...

    Maybe:

    =SUMPRODUCT((A6:D6>B6:E6)+0)
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs / sumproduct...

    Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.

  5. #5
    New Member
    Join Date
    Oct 2010
    Location
    Manchester, UK
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs / sumproduct...

    Thanks guys...
    Marcelo's solution works!
    I think Aladin's method fails as it compares the Home with the Away Result (as I wanted), but then also compares that Away results with the next Home result... thus counting some noe results.
    e.g. There are 2 results here, 3-2 and 1-0, thus there are 2 "home wins".
    Marco's system would see; 3 > 2, 2 > 1 and 1> 0, i.e. 3 "home wins".
    5 | 6 | 5 | 6
    3 | 2 | 1 | 0

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by a massive zebra View Post
    Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.
    1. A relational comparison (with >, <, so on) delivers either a TRUE or a FALSE.

    2. SUMPRODUCT works (needs) only with numbers.

    3. An operator like + acts as a coercer.

    4. TRUE+0 ==> 1; FALSE+0 ==> 0. (By the way, TRUE is 1 and FALSE is 0 in Excel.)

    5. Hence +0. That is, all comparison results are transformed (coerced) into 1's and 0's.

    6. SUMPRODUCT sums those 1's and 0's. The sum that obtains is effectively the count we are looking for.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by poolio View Post
    Thanks guys...
    Marcelo's solution works!
    I think Aladin's method fails as it compares the Home with the Away Result (as I wanted), but then also compares that Away results with the next Home result... thus counting some noe results.
    e.g. There are 2 results here, 3-2 and 1-0, thus there are 2 "home wins".
    Marco's system would see; 3 > 2, 2 > 1 and 1> 0, i.e. 3 "home wins".
    5 | 6 | 5 | 6
    3 | 2 | 1 | 0
    In that case, we need to use the 5 and 6 codings.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,275
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by poolio View Post
    Thanks guys...
    Marcelo's solution works!
    You are welcome. Thanks for the feedback.

    M.

  9. #9
    Board Regular
    Join Date
    Dec 2009
    Location
    Yorkshire, UK
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by Aladin Akyurek View Post
    1. A relational comparison (with >, <, so on) delivers either a TRUE or a FALSE.

    2. SUMPRODUCT works (needs) only with numbers.

    3. An operator like + acts as a coercer.

    4. TRUE+0 ==> 1; FALSE+0 ==> 0. (By the way, TRUE is 1 and FALSE is 0 in Excel.)

    5. Hence +0. That is, all comparison results are transformed (coerced) into 1's and 0's.

    6. SUMPRODUCT sums those 1's and 0's. The sum that obtains is effectively the count we are looking for.
    Thanks very much for the explanation.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Countifs / sumproduct...

    Quote Originally Posted by a massive zebra View Post
    Thanks very much for the explanation.
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •