+ Reply to Thread
Results 1 to 7 of 7

Calculation of runs scored for and against in Cricket

  1. #1
    Registered User
    Join Date
    01-25-2020
    Location
    Bangladesh
    MS-Off Ver
    2007
    Posts
    33

    Calculation of runs scored for and against in Cricket

    Hi,

    Please download the sample workbook first.

    As you can see in my workbook "B9" means all the run South Africa scored against all the other teams.
    and "C9" means all the other teams scored run against South Africa.

    Its just a sample, so I want to create a formula for "B9" and "C9".

    My english is poor thats why it is difficult for me to explain, I hope you understand.


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Calculation of runs scored for and against in Cricket

    Maybe, simplest solution:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-25-2020
    Location
    Bangladesh
    MS-Off Ver
    2007
    Posts
    33

    Re: Calculation of runs scored for and against in Cricket

    Your formula is correct but it also counts if in a row where South Africa is not a part of. Please see the updated attachment.
    Attached Files Attached Files
    Last edited by AliGW; 02-09-2020 at 05:53 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: Calculation of runs scored for and against in Cricket

    What is wrong with the SUMIF option? That produces the correct result, as far as I can see.

    Please clarify what the issue is and with which formula.

    This seems the most obvious solution to me for runs against:

    =SUMIF($C$3:$C$6,A9,$B$3:$B$6)+SUMIF($A$3:$A$6,A9,$D$3:$D$6)

    and for runs for:

    =SUMIF($C$3:$C$6,A9,$D$3:$D$6)+SUMIF($A$3:$A$6,A9,$D$3:$D$6)
    Last edited by AliGW; 02-09-2020 at 06:07 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    01-25-2020
    Location
    Bangladesh
    MS-Off Ver
    2007
    Posts
    33

    Re: Calculation of runs scored for and against in Cricket

    Sorry I think its my fault that I was unable to make you understand.
    So that I redesign the table for you.I hope it will make you understand.

    Here, "C10,C11,C12,C13,C14" and "D10,D11,D12,D13,D14" are the expected results.
    I also applied your formula to make you understand what is going wrong.



    Rules-1: "For" means respective team scored runs against all other teams.
    Rules-2: "Against" means all the other teams scored runs against that respective team.
    Rules-3: If respective team is not part of any game then runs of those games will not be counted in any way.


    Thanks.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: Calculation of runs scored for and against in Cricket

    Yes, and that is precisely what my formulae do. The formula you have used for against is not mine.

    Use this instead:

    =SUMIF($B$3:$B$7,$B10,$E$3:$E$7)+SUMIF($D$3:$D$7,$B10,$C$3:$C$7)

    Excel 2016 (Windows) 32 bit
    E
    F
    9
    FOR (SUMIF)
    AGAINST (SUMIF)
    10
    678
    466
    11
    378
    354
    12
    390
    489
    13
    290
    280
    14
    203
    350
    Sheet: Sheet1
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-25-2020
    Location
    Bangladesh
    MS-Off Ver
    2007
    Posts
    33

    Re: Calculation of runs scored for and against in Cricket

    Quote Originally Posted by AliGW View Post
    Yes, and that is precisely what my formulae do. The formula you have used for against is not mine.

    Use this instead:

    =SUMIF($B$3:$B$7,$B10,$E$3:$E$7)+SUMIF($D$3:$D$7,$B10,$C$3:$C$7)

    Excel 2016 (Windows) 32 bit
    E
    F
    9
    FOR (SUMIF)
    AGAINST (SUMIF)
    10
    678
    466
    11
    378
    354
    12
    390
    489
    13
    290
    280
    14
    203
    350
    Sheet: Sheet1
    Sorry, I didnt noticed your solution
    Your formula works just fine.
    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 10-08-2019, 11:30 AM
  2. [SOLVED] User form runs one calculation before a second?
    By rob_h in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2018, 04:32 AM
  3. [SOLVED] Calculating winning/drawing/losing runs & goal scoring runs
    By gko_87 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-25-2017, 06:47 AM
  4. VBA runs slow locally, runs fine when connected remotely
    By jbzy324 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2015, 10:05 PM
  5. Want to cricket a Excel Based Cricket Score Sheet
    By amitmvyas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2014, 12:25 PM
  6. [SOLVED] Split runs from wickets in cricket score
    By jimb1993 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-20-2013, 10:55 PM
  7. Add calculation to a macro that runs automatically when a cell value changes.
    By redhead1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2013, 07:26 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1