Hi. I have a big problem here. Here's the thing:
I have an Excel file to keep track of my football betting scores. How the format goes is like this:
| Competition | Match | Bet | Result | Placeholder |
In this Placeholder column, I use a function to input "1" if the value in the Result column is negative, "2" if the value in the result column is positive.
E.g.
----------
Game A: Result = 5.5 / Placeholder = 2
Game B: Result = -5 / Placeholder = 1
To avoid having to scroll down, I input my data horizontally. Meaning say if I input data from row 1 column 1, 2 and I reach row 39, I continue from row 1 column 5, 6.
E.g.
----------
| Column A | Column B | Column C | Column D | Column E |
| Game 1 | Result 1 | *blank* | Game 4 | Result 4 |
| Game 2 | Result 2 | *blank* | Game 5 | Result 5 |
| Game 3 | Result 3 | *blank* | Game 6 | Result 6 |
In order to keep track of how many games I won or lost, I designated a cell to keep track of my win/loss record by using the value in the Placeholder column. If the value is 2, I take those 2s, add them together, then divide it by 2 to get my actual number of wins. If the value is 1, I take those 1s and add them together to get my total number of losses.
E.g.
----------
Placeholder
1
2
1
Total wins = 1 / Total losses = 2
Currently, the formula I use goes like this
Wins cell
(SUM((SUMIF(I1:I39, "=2")) / 2)) + (SUM((SUMIF(P1:P39, "=2")) / 2)) + (SUM((SUMIF(W1:W39, "=2")) / 2))......
Losses cell
(SUMIF(I1:I39,"=1")) + (SUMIF(P1:P39, "=1")) + (SUMIF(W1:W39, "=1"))......
So, as you can probably see, it's getting tedious to have to constantly update the formula of the win/loss cells whenever I scroll horizontally to start on a new set of columns. I tried modifying the SUMIF function, tried using the SUMPRODUCT function and stuff to no avail.
So, my question is, is there any way of shortening the formula in the win/loss cells such that it can continue to add itself while minimizing the number of keystrokes I need to input for every update (like, just need to add a new *letter*1:*letter*39)? I don't wish to just select the whole worksheet because firstly, it won't be accurate since my Result column may have an exact "2" or "1", making the calculations inaccurate. Secondly, it will dramatically increase the file size and processing time every time I update something.
Oh, and one more thing. I don't have any column header like "Competition", "Result" or what identifying any of my columns. Cause I don't want to have to make it look ugly with those headers and I don't want to have to copy them repeatedly across multiple columns.
Sorry if I'm too long-winded.
Bookmarks