Results 1 to 9 of 9

Wanting to do SUMIF across multiple columns

Threaded View

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    5

    Wanting to do SUMIF across multiple columns

    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.
    Last edited by Vx22B012; 11-30-2012 at 11:52 AM.

Thread Information

Users Browsing this Thread

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

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