(No VBA) Count of a Text Value in a Table Column, Running Total of Occurrences by Rows in a Separate Column

[DaddyO]

In a table with structured reference:

One column with rows containing multiple text values.

In another column, a running total by row of the number of occurrences in the column one of those text values.



WorL, Running Total Wins
W, 1
W, 2
L, 2
L, 2
W, 3
L, 3
L, 3
L, 3
W, 4

The rows in a third column contain unique incrementing GameID numbers.

I'd like to base the running total in each row comparing the [GameID] value in each row to the value in the current row like this: "<=[@[GameID]]" . But my attempts to create a formula that works have not worked so far.

I do not want to employ VBA, only Excel functions.

Any help is appreciated.

 

[Peter_SSs]

In that case, instead of just uploading the image of the problem, can you share a copy of an actual file (with any sensitive data removed/disguised) so that we can try to identify the problem with the formula?

 

[DaddyO]

Sorry, just realized that I had confused Custom Formatting with Conditional Formatting. Same answer though, no.

 

[DaddyO]

Sent you a private message with a link to the file.

 

[Peter_SSs]

Sent you a private message with a link to the file.

In future, please make sure any shared sample data is suitable for all forum members to access and provide any link publicly in the forum as we like to ensure all readers have access to all the information if possible. It is sort of what #4 of the Forum Rules is trying to cover.

In any case, if I copy the formula directly from post 7 and paste into cell S1 of sheet 'V_GmResults' of your sample file, I get identical results to what was already in column S with the formula you were using (at least down as far as the data goes).

If I use a similar structure to what you already have in that column ..

=IF(ROW()>Tm_NoGms+1,"",COUNTIF(INDEX([Dec],1):[@Dec],"W"))

.. then I get identical results all the way down the column.

The only way I can see that you would get a column of FALSE results is if you left out the red part of the formula above (but that would contradict your earlier statement: "I just copied and pasted your formula.")

 

[DaddyO]

Re: Forum Rule #4 , duly noted. Sorry for breaking protocol.

I'll be tomorrow morning checking why things are looking different on my end, user error or some other reason.

 

[Peter_SSs]

OK, hope it will be resolved but post back if you are still having problems.

 

[DaddyO]

In any case, if I copy the formula directly from post 7 and paste into cell S1 of sheet 'V_GmResults' of your sample file, I get identical results to what was already in column S with the formula you were using (at least down as far as the data goes).

If I use a similar structure to what you already have in that column ..

=IF(ROW()>Tm_NoGms+1,"",COUNTIF(INDEX([Dec],1):[@Dec],"W"))

.. then I get identical results all the way down the column.

The only way I can see that you would get a column of FALSE results is if you left out the red part of the formula above (but that would contradict your earlier statement: "I just copied and pasted your formula.")

Copied and pasted your original formula in this thread and it worked fine. I had indeed copied and pasted it before, so I must have failed to copy it completely, or correctly, or who knows what.

Again, thanks for your help, and I will post a public form of the spreadsheet shortly.

 

[DaddyO]

Here's a link to the full workbook showing the pertinent worksheet and columns with other columns hidden (though there is nothing personal in them).

https://1drv.ms/x/s!ArIAG4a3p8D7h5cAt54LBiGHefwWiA

 

[Peter_SSs]

Copied and pasted your original formula in this thread and it worked fine. I had indeed copied and pasted it before, so I must have failed to copy it completely, or correctly, or who knows what.

Again, thanks for your help, and I will post a public form of the spreadsheet shortly.

You're welcome. Glad you got it sorted.

Here's a link to the full workbook ..

Since you have things working I don't think we actually need to see the workbook now but note that the link does not allow access to the file for me: "Sorry, we can't open your workbook in Excel Online because it exceeds the 5 MB limit."

 

[DaddyO]

Since you have things working I don't think we actually need to see the workbook now but note that the link does not allow access to the file for me: "Sorry, we can't open your workbook in Excel Online because it exceeds the 5 MB limit."

Ah, got it.