I need a formula to SUM the Bank and ROI columns by day / week, etc. for single and Multi Bet 2,3,4

[noporschenolife]

I am very weak in excel, and stuck at the last stage in the table, so I am looking for help on the Internet. I have created a "Bank + ROI" Sheet for keeping statistics. Everything is simple here: there is a summation of money by days / weeks / months / years / all time and the ROI percentage of each bet also by days / weeks / months / years / all time. When I select single bets on the "Bets" Sheet, then my formula sums everything up correctly, but when I select "M" (Multi Bet 2,3,4+) express bet in the "Bets" Sheet and the rates appear from 2-4 + ( depending on the selected type of bet), then empty cells appear in the Sheet "Bets" -Date, and my formula no longer fits. Is it possible to find a beautiful formula to solve my problem, so that the sum goes further down the column, despite the empty cells in the Date, or do I do something for this. While my knowledge is not enough to find the answer, perhaps there is a guru of this program here. ----------------------------------------> https://dropmefiles.com/Nn3ra 

THX for any help

BetsBank+ROI

[#2 · Accepted solution]

@noporschenolife Here is a formula for the day by day running total and you should be able to modify for the other columns:

=IF(Bets!A27="", "",IF(Bets!A27=MAX(Bets!A$11:A26),Bets!AC27+INDEX(Bets!AC$11:AC26,MAX(ROW(Bets!A$11:A26)*ISNUMBER(Bets!A$11:A26)-ROW(A$10))),Bets!AC27))

Basically it checks if this date is = to the max of all previous dates (i.e. doesn't care if there are spaces) then if so it INDEXes the column of data based on the last row that ISNUMBER.

BTW if you are using Excel 365, you might be able to make an easier formula using FILTER and LET functions.

[#3]

Now I will try to use your formula and see how it works. And my excel says that this "," character in the formula is wrong and I have to use this ";" character. Anyway, I can't believe someone answered me on the internet and helped, I'm very glad. The fact that you described how this formula works sounds simple, but I myself would not have thought of this myself, since I am simply not yet familiar with these formulas (MAX, INDEX and ISNUMBER, as well as what you wrote about filters) ... You are smart. If the formula works, how can I thank you?

[#4]

You're very welcome and if you have any questions about how they work or problems getting them to work just let me know. Your appreciation and thanks is all we ask for

[#5]

Okay, but I have a more interesting proposal for you. Let's agree on 2.5% of my bank(net money without tax on winnings and interest on the transfer) in 2 years, so to speak, with witnesses on this site. Also, every month I can send my table with attached screenshots from the bookmaker's office, from the moment I start playing. This will be an additional incentive for me to earn more. The only thing I need is a contact with you, which you will never delete and will always be available (write me your name and your mail or any other social network in private messages). If I have more questions about the table, I will write to you here. Thank you for everything.

[#6]

wow that is quite the offer but we just volunteer here. feel free to post questions here and there are many volunteers even better than me that can help or you can direct message me if you questions. I wish you all the best would love to hear how it goes.

[#7]

Yes, I understand that there are smarter people, and that people here help people just like me, but you helped me in solving the problem, not them, that's the difference. But this is your decision, and if you change your mind, I will only be glad.
I changed the formulas for my language and changed the symbols from "," to ";" and stretched it to the entire column and it works fine. Then I began to try to change it under the "Bank Week" column, but I made a mistake somewhere and did not fully understand the last part in the formula, or rather I understand it, but I can correctly apply it under "Bank Week".

That's what I did:

= IF (Bets! A12 = ""; ""; IF (WEEKNUM (Bets! A12) = MAX (WEEKNUM (Bets! A $ 11: A11)); Bets! AC12 + INDEX (Bets! AC $ 11: AC11; MAX ( ROW (Bets! A $ 11: A11) * ISNUMBER (Bets! A $ 11: A11) -ROW (A $ 10))); Bets! AC12))

We need to implement this part by applying it to your formula above:

IF (blablabla; "Bets! AC12 + 'Bank + ROI'! B2"; Bets! AC12)

I will reload the xlsx file, if I have time, see what is wrong there, or do as you can (my logic is not enough to do it right).
Thanks Matt.

download.xlsx   

@mtarler