Forum Discussion

Copper Contributor

Jan 07, 2022

Solved

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

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: the...

excel

mtarler
Jan 07, 2022
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.

mtarler

Silver Contributor

Jan 07, 2022

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.