Forum Discussion

scrail2004's avatar
scrail2004
Brass Contributor
Nov 17, 2026

Accum Wins from Competitor-Day Table

I track the bi-monthly Grand Sumo tournaments.  Sumo lists the competitors and results in a strange way, at least by Western standards.  So the attached is a REALLY scaled down version of my tracking workbook.

I am looking for a formula that will tell me each wrestler's accumulated wins for each day of the tournament.

On the left side of the attached is a table showing results for each day...wrestlers in the left column, days across the top.  An "O" is a win and an "X" is a loss. (I only need to count the accumulated wins, because I can calculate the losses from there - total days competed so far, less wins.)

From the data in the table, I need to populate column I, "Accum Wins", for each wrestler on each day.

It seems crazy, but the order in which each day's wrestlers appear (column H) change by day.

So on Day 1, Davidson would have 0 accumulated wins, Billings would have 0, Cooper 1, and so on.

On Day 2, Billings would have 1 accumulated win, Davidson would have 0, Flanders 1 and so on.

I know this is an easy one for y'all.  I look forward to your help!

9 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor

    Assuming a couple of things:

    a) you have Excel 365 and you can use the new formulas like GROUPBY

    b) the format of the data is how I set it up in the screenshot below

    then the formulas are shown in I1 and I8 (for the cells directly below them respectfully).  They are the same concept but 1 uses MMULT and the other uses BYROW, but in both cases that basically collapses the columns into a singlle column with either a value of 1 if there is an "o" on that row or a Zero (0) if not. Then in both cases the GROUPBY function does the work of collating the results per person/name.  Here are the formulas for cut/paste:

    =GROUPBY(A2:A21,MMULT(--(B2:F21="o"),--TRANSPOSE(B1:F1<>"")),SUM,0,0)
    
    =GROUPBY(A2:A21,BYROW(--(B2:F21="o"),SUM),SUM,0,0)

     

    • scrail2004's avatar
      scrail2004
      Brass Contributor

      The moderator would not let me respond in a single response to everyone who offered a solution, so I am responding to each of you individually.

      Thank you for your solution, but I'd prefer not to have to rearrange the data table.  I've laid it out as Grand Sumo does and my goal is to reduce the number of steps, not increase.

  • There's a snag in the two formula suggestions above, and it's worth fixing before you copy them down — the COUNTIFS ones won't run as written. In Excel every range in COUNTIFS has to be the same size, rows and columns, or you get a #VALUE! error. The name range there is one column wide and the results range is six columns wide, so that version can't work.

     

    The INDEX idea in the first reply is the right direction, it just needs the end of the range tied to the day you're counting up to. If your results run in B:G with the day labels in row 1, names in A and the wrestler for the summary row in H2, try this in I2 and copy it down:

     

    =COUNTIF(INDEX($B$2:$G$100,MATCH(H2,$A$2:$A$100,0),1):INDEX($B$2:$G$100,MATCH(H2,$A$2:$A$100,0),MATCH(J2,$B$1:$G$1,0)),"O")

     

    Here J2 would be the day label for that summary row — Day 1, Day 2, and so on. MATCH finds the wrestler's row, the first INDEX starts the count at Day 1, and the second INDEX stops it at the day in J2, so the total grows day by day instead of counting the whole tournament at once. The changing order in column H doesn't matter, because everything is matched by name.

     

    If your day labels sit somewhere other than row 1, just point the last MATCH at that row instead. And if you can re-attach the workbook Riny asked for, it's easy to check the exact columns against it.

  • You can solve this using SUMPRODUCT and INDEX/MATCH to find each wrestler's row and count all their wins up to the current day.

     

    Assuming:

     

    - Column A contains wrestler names.

    - Columns B:G contain tournament results (O = win, X = loss).

    - Column H contains the wrestler name for the daily summary.

    - Column I is Accum Wins.

     

    Enter this formula in I2 and copy it down:

     

    excel

     

    excel

    =COUNTIF(INDEX($B$2:$G$100,MATCH(H2,$A$2:$A$100,0),0),"O")

     

     

    How it works:

     

    - `MATCH` finds the wrestler's row in the results table.

    - `INDEX` retrieves that wrestler's results across all tournament days.

    - `COUNTIF` counts the number of wins ("O").

     

    Important: This formula counts wins across the entire tournament. If you want accumulated wins only through the current day, the formula needs to account for which day is being displayed.

     

    For that, you can use:

     

    excel

     

    excel

    =COUNTIF(INDEX($B$2:$G$100,MATCH(H2,$A$2:$A$100,0),1):INDEX($B$2:$G$100,MATCH(H2,$A$2:$A$100,0),MATCH(G$1,$B$1:$G$1,0)),"O")

     

     

    Adjust the day header reference to match your workbook.

     

    This will calculate each wrestler's cumulative wins up to the specified day, regardless of how the wrestler order changes in column H.

    • scrail2004's avatar
      scrail2004
      Brass Contributor

      The moderator would not let me respond in a single response to everyone who offered a solution, so I am responding to each of you individually.

      Your solution worked.  Now I’ll work on fitting it into my actual workbook.

      I have attached the Excel file that did not make it through the first time (now with a tab showing your solution).

      Thanks for the help!

  • ciqyf8's avatar
    ciqyf8
    Occasional Reader

    You can use SUMIF to calculate each wrestler's accumulated wins, even when the wrestler order changes each day.

     

    Assuming:

     

    - Column A contains wrestler names.

    - Columns B:G contain daily results (`O` = win, `X` = loss).

    - Column H contains the wrestler name for the selected day.

    - Column I is Accum Wins.

     

    Enter this formula in I2:

     

    excel

     

    excel

    =COUNTIFS($A$2:$A$100,H2,$B$2:$G$100,"O")

     

     

    However, if you need accumulated wins only up to the current day, use a formula that counts wins in the relevant day columns.

     

    For example, for Day 2:

     

    excel

     

    excel

    =COUNTIFS($A$2:$A$100,H2,$B$2:C$100,"O")

     

     

    Adjust the ending column as the tournament progresses.

     

    This matches wrestlers by name, so their changing order won't affect the results\!

    • scrail2004's avatar
      scrail2004
      Brass Contributor

      The moderator would not let me respond in a single response to everyone who offered a solution, so I am responding to each of you individually.

      I couldn't get your solution to work.  It worked for Day 1, but didn't work when I modified the formula for Day 1 through 2.  I have attached the Excel file that did not get through the first time (now with a tab showing your solution).  Hopefully, seeing it will help.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    scrail2004​ There is no attachment. Please try again or if the system doesn't allow you, provide a link that give full access to it on OneDrive or similar.

    • scrail2004's avatar
      scrail2004
      Brass Contributor

      The moderator would not let me respond in a single response to everyone who offered a solution, so I am responding to each of you individually.

      Sorry for the attachment not working.  I have attached it again.  This version includes a worksheet for the mixrotechlo512 and ciqyf8 solutions.  Please let me know if it doesn't show up this time.