Search code examples
excelsumformulaadditionsumifs

Summarize value from a series based on condition(s)

Currently I have an Excel-file where I track my wins/losses from betting with a friend. The bets are placed be either of us as you can see in Column K

I need help with developing a formula (Assuming it will be an SUMIFS) where it will calculate the running total for each person (Farzam & Leon). The idea is that you can see how much profit you are in total after each game. I need to code to work correctly since I will be creating a graph based on this data.

So the idea would be something like this: Code checks if Bet is placed by Farzam or Leon. Then it will add the value for the M-Column (M9 is the first one). Then the code needs to take this value and add it based on the previous value that was won/lost by Farzam or Leon.

You'll need to write code for two columns (Y and Z). Should be fairly equivalent.

enter image description here

Enclosed you'll find a screenshot of the document so you get an better idea. Let me know if anything is unclear and I'll try to explain better.

Currently I have the following code, but it's not working like my description. =SUMIFS($M9:M16;K9:K16;"Leon")

Solution

  • If I understand correctly, you want a running total, meaning only to sum the range from this line backwards, and account for whom has made this bet.

    enter image description here

    In this case, the following formula can be used:

    =SUMIF($M$9:$M9,"Leon",$K$9:$K9)

    Note the $ signs meaning these are static values. When dragged down with the fill handle, the formula will start counting up the range $M$9:$M10 then 11, 12 etc.` and only calculate from that line backwards. This results in the above running total picture.

    The way you did it:

    =SUMIFS($M9:M16;K9:K16;"Leon")

    Is close, but will only calculate the entire range at once, so always have one static output of the complete total.