Calculate summary data for Tournaments categories w/ total bankroll

Hi, Would like to get help,
Calculate summary data from Tournaments sheets, to bankroll Catagories, columns W:AB rows 3:42 Along with my Total Bankroll & Sugg. Buy In: base on my 1st deposit Amt. on All transactions column D & Column T on my tournament.


please feel free to ask any question. Cheers

As there were no manually calculated results placed in Y3:Y42 or AB3:AB42, the results yielded by the formulas will need to be verified.
1. For "Played": =SUMPRODUCT(--(ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments]))))
2. For "Revenue": =SUMPRODUCT((ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Prize]))
3. For "Cost": =SUMPRODUCT((ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Buy-in + Rake*Rebuy]))
4. For "Today's Profit": =SUMPRODUCT((ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Profit])*(INT(tbl_Tournaments[Start])=TODAY()))
5. For "Profit": =SUMPRODUCT((ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Profit]))
6. For "ITM%": =SUMPRODUCT((ISNUMBER(SEARCH(W2,tbl_Tournaments[Tournaments])))*(tbl_Tournaments[Prize]<>""))/Y3

Mate, I made a list of the count of the tournaments. I break it down in the Catagory of which they should fall into and the count number per each, that are in the Tournament sheet. here it is let me know if your able to see it clearly.

333.png

Speed Racer, High Roller and Festivals series seem to be correct

I think that this was the reason I suggested manually entering a category of each record when worked on this last time.
I had written a ridiculously long formula to separate things like "47-KO: $3.21 Bounty Hunters Warm-Up" and "$1.11 Bounty Hunters Big One".
The formula still wasn't always correct.
I added a Category column (E) which is populated using data validation to make things a bit easier.
Once you have filled in the Category column, let us know so that we can adjust the formulas on the Bankroll sheet.

good morning mate, mate
thank you, okay is manually entering a category of each would consist of having the list of games ?
I mean because if that is going to be an issue for when new festivals or series come, is not going to be like this "46-KO" is going to be different type of name every time. okay mate let me work on that. and will re-upload

The numbers in the "Played" rows now match those in post #4.
The formulas in most of the other rows are also changed.
"Played": =COUNTIFS(tbl_Tournaments[Category],W2)
"Revenue": =SUMIFS(tbl_Tournaments[Prize],tbl_Tournaments[Category],W2)
"Cost": =SUMIFS(tbl_Tournaments[Buy-in + Rake*Rebuy],tbl_Tournaments[Category],W2)
"Today's Profit": =SUMPRODUCT((tbl_Tournaments[Category]=W2)*(tbl_Tournaments[Profit])*(INT(tbl_Tournaments[Start])=TODAY()))
"Profit": =SUMIFS(tbl_Tournaments[Profit],tbl_Tournaments[Category],W2)
"ITM%": =SUMIFS(tbl_Tournaments[Prize],tbl_Tournaments[Prize],"<>",tbl_Tournaments[Category],W2)/Y3

One section at a time please.
As to total bankroll it seems that should come from Info!Y3 which is: =SUM(SUM(tbl_Tournaments[Profit]),SUM(tbl_AllTransacations[Amount]))
This seems to correct the #REF errors in several other cells, however I am not sure that the amounts are correct so you will need to check.

Mate, after checking amt. balance in tournament sheet. when I input in buying 1 dollar my balance in column T is not correct.
I think is calculating the Rake amt. which I delete that column from Tournament. it would only be amount I stake.
when i Input $1.00 for buying my balance shows $4,998.71 its taking the $-.39 for rake i think.

I see whats going on mate, column I share, its minizing the share mount i have 29% 22% 51% example -$.29 , -$.22 and -$.51
mate how can i remove the % on markup column H i just want the 1.07, 1.10, 1.12 not the % i when thru all the formats on the list and custom too.

Note that I typed 100 in cell H60 and pressed the Ctrl + c keys to copy. I then selected cells H2:H56 > Paste (from the Home tab) > Paste Special > Multiply > OK
Are the values in H2:H56 now correct?

If you change the format of Balance in $#,##0 it will show $4,999.

No the format of the amounts in column Balance is in your example $#,##0.00, In that case it is impossible that the amount shows $4,999. It will always shows the cents also.

No I can not determine a picture, please upload a sample sheet and shows the unexpected result.

In that case remove in Column S [@Share])*([@Rebuy] from the formula.
The result in Column S will be 29% of $1.00 = $0.29 higher.

Mate, that seems to work, only what do I do to still get my rebuy amt. to calculate.
lets say if i add 1 rebuy in column L it should be 2. but if i remove the above it won't show.

Nice thank you Hans, I was able to fixed that. I change it to =[@Prize]-([@[Buy-in]])*([@Rebuy]+1)

think is working now

Thanks for your feedback and glad to have helped.

If there are any further questions, don't hesitate to post them here.

Please consider adding reputation to any helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Thanks for the feedback and rep .

Is the workbook in post #27 the latest version?
If so, please be specific as to which stats on the bankroll sheet need to be worked on and what the correct values should be.

yes, Mate is the latest version.
Todays Buyins P4 & Todays Cashes: values should be for the current day playing
for Dailey & month Profit I12 and I13: profit and stake for the current day
month tourney profit I14: profit for the month

Largest tourney wins AE3 to AE41: players with the highest to lowest wins

Mate,
Not sure why when, I change Network in tournament column B3 to GG My deposit deletes from deposit column.

Please give us some records on the Tournaments sheet that have dates that are in the current month and also today.
Please tell us the numbers that are expected for cells P4, I12 etc.
I assume that the tournament wins should come from the Prize column: =IFERROR(LARGE(tbl_Tournaments[Prize],ROWS(AD$3:AD3)),"")
The names in column AE can be obtained using:

Formula:

Please Login or Register  to view this content.

The formula in cell U2 on the Tournaments sheet should have been:

Formula:

Please Login or Register  to view this content.

Somehow it had gotten changed to:

Formula:

Please Login or Register  to view this content.

I am going to use a trick that I just learned from AliGW that will hopefully prevent that from happening in the future:

Formula:

Please Login or Register  to view this content.

Thank you mate,
expected values for cells P4: should be $200 its adding .46 cents from rake.
P5: should be $1500,
I12: profit $1300
Todays Stake I13: $200
Day N3: can we get the count of days played
Month Profit I14: buying/wins amt. to profit its bit dificult to give you the value since is adding rake & rebuys
In tournaments row 56 column rank 1 Ft 1 in column Q56 don't show the 1 win


I try removing [@Share] from formula in column Y. see if that would fixed it, it didn't. I left it how it is. don't want to mess up the re-buys. its adding the $.46 cents of rake from share column.

Mate, Have a question about the chinese Zodiac, can we add a conversion in buying and prize for when i Input buying/prize in Yuan amt, it convert it to dollars.

I update the dates

P4: =SUMPRODUCT((tbl_Tournaments[Buy-in])*(INT(tbl_Tournaments[Start])=TODAY()))
"P5: should be $1500," Based on values found where?
I12: =SUMPRODUCT((tbl_Tournaments[Profit])*(INT(tbl_Tournaments[Start])=TODAY()))
I13: =P4
N3: ="Days - "&MAX(tbl_Tournaments[Days])
I14?: =SUMPRODUCT((tbl_Tournaments[Profit])*(INT(tbl_Tournaments[Start])>=EOMONTH(TODAY(),-1)+1)*(INT(tbl_Tournaments[Start])<=EOMONTH(TODAY(),0)))
Wins column: =IF(Tournament!P2=1,1,"") somehow it was showing: =IF(Tournament!P144=1,1,"")
Will need a yuan to USD conversion rate.

Mate,
P5 should be based on values from prize for the current date 6-16-23

I notice row 37 and 43 adding 1 in column Q for win, when the rank is 8 and 9 it should only be in final table

Cells AB21:AB25 on the Bankroll sheet have been updated to include the conversion of yuan to USD.
Note that the value of yuanconv is in cell AQ2 on the Info sheet, in case it need to be changed in the future.
P5: =SUMPRODUCT((tbl_Tournaments[Prize])*(INT(tbl_Tournaments[Start])=TODAY()))
Column I on the Tournaments sheet: =IF([@Rank]=1,1,"")

Showing #REF because somewhere along the way the formula for that column became: =SUM(T1,[@Profit],[@Deposit],IF(ROW()-1=MAX(ROWS([Network])),#REF!,0))
I can't remember what should have been in the place of the #REF in the formula.
Suggest that you look back at the previous thread concerning the Staking workbook and see.
As to the formula in column S on the tournaments sheet: =[@Prize]-([@[Buy-in]])*([@Rebuy]+1)*IF([@Category]="Chinese Zodiac",yuanconv,1)

Hi, Mate thank you.
I have try that before in regards #REF! i try changing the formula from

=SUM(T1,[@Profit],[@Deposit],IF(ROW()-1=MAX(ROWS([Network])),#REF!,0))
from to this
=SUM(T1,[@Profit],[@Deposit],IF(ROW()-1=MAX(ROWS([Network])),tbl_CashGames83[Profit],0))
I get this message when changing it.

honestly not sure what happen Mate, god is good I added the ' infront of the = like what the picture says and didn't work. then i remove it and by mistake press a key not sure which one and it gave me this formula.
=SUM(T1,[@Profit],[@Deposit],IF(ROW()-1=MAX(ROWS([Network])),1,0)) and this seems to remove the #REF! Wow nice i will upload.
not sure what i press maybe tabe don't know but is fixed now.

Mate,
after testing Yuan to US dollars not sure is adding correct. when entering the buying in Yuan $715 profit shows -$100.10 which is correct but when I add prize $715 then profit changes to $614.90 and adds $614.90 to the total bankroll in usa dollars
row 12

dd.png

Since there isn't a tbl_CashGames83 in this workbook I would suppose that this formula came from the other workbook.
Note that in the file attached to post #44 cell T57 is 1 dollar more than cell T56 even though S57 is zero.
It seems to me that the current balance is the previous balance plus the profit plus a deposit, if one was made, and the formula for the balance column should be: =SUM(T1,[@Profit],[@Deposit])
The issue with the profit formula is my fault, it should have been: =([@Prize]-([@[Buy-in]])*([@Rebuy]+1))*IF([@Category]="Chinese Zodiac",yuanconv,1)

As to post #48, assuming that row 12 on the tournaments sheet was entered as yuan, then yes, when entering buy-ins and prizes for category 'Chinese Zodiac' enter them in there yuan amounts.

As to post #49, the first graph I see is profit by number of games played. Is that the one that you want?

In order to make a chart based on number of games an index column (AC) is added to tbl_Tournaments populated using: =SUM(AC1,1)
The chart references the Balance and Index columns.
Since all of the tournaments in post #54 include the "¥" symbol I changed the formula in the profit column to read: =([@Prize]-([@[Buy-in]])*([@Rebuy]+1))*IF(ISNUMBER(SEARCH("¥",[@Tournaments])),yuanconv,1)
Also made some changes to the Chinese Zodiac section on the Bankroll sheet.

Mate, I made changes to the workbook, can we work out of this one.
column L for rebuys, can we remove the adding the rebuy amt. can it only be buying for staking it. would be only amt. stake don't matter how many times they rebuy, if they enter 2 times only thing my % cuts in half.
I added Duration time the tournament last for, from the Start Time in column C, is it possible to write a formula where, I enter the duration and calculates the End Time and date, on column D?.
I enter 1 Chinese Zodiac the last one in row 25, I notice is adding it to High in profit by Buy-ins in bankroll F32. is showing played 20 when is 24, I know it has to do with the buyins amt. let me know what I need to do to correct it, amounts are going to be from any cents to high amouts depending on the share I buy for any particular tournament buying.

Please see Pivot table I spend few hours arranging the columns, have a question on the Balance, where it has 920.57 can it be the -100.01 instead?
why in Balance column after 81.26 jumps to 37.04 and not 79.41 like in Tournament Balance? so

Mate, Just notice a glitch in Todays Buyins, Its adding the last tournament Chinese zodiac in dollars Yuan 715 its adding to todays buying $723.37 dollars

788.png

As to post #57, I assume that you want the $ markers and I don't know how that was done. You'll have to find someone who knows more about graphs to say whether or not that can be done using Excel.
As to post #58:
Column L has nothing in it but the header so I don't know what you want there. Perhaps type in some results so we can see if they can be replicated using a formula.
Column D: =SUM([@Start],[@Duration])
Changed Buy-ins Played formula to: =COUNTIFS(tbl_Tournaments[stake],F28)
Changed the Show Value As of the Balance column from No Calculation to Running Total so that it would not show the Grand Total.
For some reason, not sure why, the pivot table is taking the last record of the June 17, 19:10 group and putting it before the other records in that group.
Changed Today's Buy-ins formula to:

Formula:

Please Login or Register  to view this content.

Thank you mate a buch
I will ask the person who I am watting response from on the other workbook, see if he knows about the markers.
not sure either I try many things to fixed the 19:10 group, no luck it would have to stay that way. I will add some rebuys and upload

Mate, Chinese Zodiac is Duplicating in profit by buy-ins is on chinese zodiac and High rows G31 & G32 in bankroll


Now that we have the duration time how come we don't have results for total time I19 and results for I23 in bankroll?

mate, Chinese Zodiac yuan prize is showing dollars amt. in Todays cashes & cashes, and when putting Yuan buying y715 it shows buyings as -$962.12 in cell P6

Mate, Is it possible to have a formula in column F45 in bankroll for all the players on the Tournament list, and do a search by stakes or ALL stake and ALL players? to quickly get the stats of a player before staking.

Mate, I update pivot table and remove old data that was been remember. like the names on the pictures.


000.png

just not sure why the Balance shows #N/A after moving Ft & wins into Values section

Mate, I added rebuys, it adds the amt. * rebuys entries can it only be buying an not add the rebuys to profit

I was able to make pivot table align columns when doing a refresh, I cant figure out why after making a refresh it changes the height on row 10, after unchecking in pivot table Autofit column widths on update. after saving.

just wanted to make rows even as they have many sizes

Balance now shows amounts. I changed Running Total in... to Entrants.
I19: =SUM(tbl_Tournaments[Duration])
Don't understand what you mean about G31:G32? Might help to tell us what the values should be.
It is late, I'll look at this again tomorrow, but it would help if we had manually calculated amounts to use for comparison to the output of our formulas.

Whats up, mate
you think we can continued with removing the rebuys from multiplying should only be buying amt. row 7 profit should only be $6.19 row 8 $6.71 and row 9 $2.96
Todays cashes: when entering yuan 100 it enters 100 dollars in column P5

Change the 'Cost' cells for each tournament to: =SUMIFS(tbl_Tournaments[Buy-in],tbl_Tournaments[Category],W2)
Change the formula in P5 to:

Formula:

Please Login or Register  to view this content.

how do I remove, buyins to not add up to the profit
mate, the graph don't show the No. Games any more

Profit: =([@Prize]-[@[Buy-in]])*IF(ISNUMBER(SEARCH("¥",[@Tournaments])),yuanconv,1)
Graph > horizontal axis has to reference the Index column (Tournament sheet)
As to the Largest Stake Wins add conditional formatting to the Prize column to alert user that the currency is in ¥. It will then be up to the user to multiply the prize * yuanconv (see cell AD25 on Tournament sheet)
Another column could be added to calculate the prize based on currency, however that seems excessive given the samples I have seen.

Mate, the cell you highlight in yellow. Okay when I change that to y715 I get this in Largests Stake column AD3 $715 in us dollars

this yuan conversion is very complicated mate

Add another column to the Tournaments sheet (Prize 2) and reference that in the formulas for Largest Stake Wins
=IFERROR(LARGE(tbl_Tournaments[Prize 2],ROWS(AD$3:AD3)),"")
and
=IF(AD3="","",INDEX(tbl_Tournaments[Player],AGGREGATE(15,6,(ROW(tbl_Tournaments[Player])-ROW(tbl_Tournaments[#Headers]))/(tbl_Tournaments[Prize 2]=AD3),COUNTIFS(AD$3:AD3,AD3))))

Thank you mate, let me check that out. mate what is the AA column in tournament for Inlcude?

mate, why is this error showing column H5 -3E-14 in Bankroll

The Include? column is used to display the results below the graph when tbl_Tournaments is filtered.
I see 100.05 in cell H5 on the Bankroll sheet.

Change the formula in H4:H8 on the Bankroll sheet to read: =ROUND(SUM(SUMIFS(tbl_AllTransacations[Amount],tbl_AllTransacations[Site],F4),SUMIFS(tbl_Tournaments[Profit],tbl_Tournaments[Network],F4)),2)

I have changed the data validation in cell F45 to display a list of players from Info!B2:B22.
I have updated the formula for the Start column by changing the two instances of tbl_Tournaments[Network] to tbl_Tournaments[Player]
I'll leave it to you to change the formulas for the other columns (Rank through Tournaments) by changing the two instances of tbl_Tournaments[Network] to tbl_Tournaments[Player] in each of those formulas.

Mate, is done will upload for you to check everything is okay. I changed all

Mate, On the dropdown list for players, is it possible to search name by typing 1st letters auto fill or auto populate, since list is going to keep growing it would be hard to find using dropdown.

P.S. Mate I update the graph! huh what you think, Nice

I have read that some of the newer versions of Excel come with autocomplete dropdowns.
I don't have one of those versions and don't know how you could autocomplete the dropdown other than using VBA of which I know too little to help.
I copied the formulas in row 53 down to at least row 100.
You'll need to attach an updated file to your next post in order for us to see what is happening in the Balance column of the pivot table.

Thank you mate, okay that question goes to the VBA person we waiting response from is been almost 3 weeks now
okay ty, will upload next

here is the upload
mate, can the Micro, low, medium and high all calculate base on the players tournaments played
Micro Example
in the MONEY %
45.71% (16/35)
Total Winnings
$187.04

same for low, medium and high

Total Winnings "ALL Games"
in the money %
16.21% (8201 / 5000

Formula for the Balance column on the Tournament sheet is now: =ROUND(SUM(T1,[@Profit],[@Deposit]),2)

Thank you mate, will download

Mate, when creating a new line in tournament sheet it always formats it this way.

Capture.PNG

can it format the same way as row 25 when creating new lines

Please attach the workbook from which the screenshot snippet in post #87 was made.

good morning mate,
Okay will upload now

In the file attached to post #89, did all of the entries in row 26 automatically display when that row was added to the table?
The attached file shows the columns that automatically populated when I removed row 26, selected AC25 and pressed the Tab key.
The only mis formatted cell I saw was AB26 ($25.00). I believe that has been corrected.

Thank you mate,
no only one that display is End time January 0, 00:00
but when I enter the other ones showing, I was asking if it can be like row 25 markup 1 entrants the number istead of november 28 and rank number instead of $355
like that I don't have to correct every time the format

Mate, how come in Finish % it shows Value! in column W in Tournaments

End time will change when a start and duration is entered.
I believe all columns now display as expected.

The #value errors correspond to rows where the rank is text.
In the attached file I removed the 'center' formatting from the rank column. Those ranks that are actual numbers shift to the right while those that are text shift to the left.
Retype the text values and the #value errors will be cleared.

Thank you mate
will see the changes now. Nice working now changed.

Mate, Can we continued with Tourney Yearly Stats in bankroll Rows AF:AJ

Need to reference the correct year cell, as in for 2024 the this segment of all formulas should be: ...YEAR(tbl_Tournaments[Start])=AF$17... and for 2025: ...YEAR(tbl_Tournaments[Start])=AF$31...

Change the references from tbl_Tournaments[Network] to tbl_Tournaments[Player] in those formulas.
I have changed the formulas in O45:O46 for you.
I hope that you have a blessed weekend.

I made a few changes, and also did a bit of staking to see how things work, and notice few things
In Tournament row 57 I made a deposit of $85 can the amt. be in row 58 since my Balance in row 57 was .31 cents then I made the Deposit
can the deposit once done in the transaction secction can it record the time like that it won't use the 1st row of the Date.
By time would be best it would always be the row for deposit. In row 102, when making a Withdraw can it do the same as deposit, because when I withdraw it only
takes the amount from the Total Bankroll but not the Tournament Running Balance. in row 102 I enter it like a buying in order to have the current balance Running
can we use some formating for Deposit to highlight in Red and green for withdraws just the way they are. Mate when I keep adding rows, I am having the same
issue where Rank format is $ not Number.
not sure the graph is working.

Change the formula for the Deposit column to:

Formula:

Please Login or Register  to view this content.

Note that you have put deposit in the category column twice for June 23rd.

If you'll upload a file with the unwanted deposit removed, I'll work on the red/green formatting.

Thank you mate, I will upload now.
I was checking the math making sure is been correct. I will work on the inputs from yesterday and today
uploading

Conditional formatting and chart should now be working.
I hope that you have a blessed night.

Thank you mate,
Im almost done inputing all the tournaments, only that I stake a few tournaments with South Korea currency not sure what the correct amout would be so my running balance could be exact. I would have to look in to everything tomorrow before I upload again

ddd.PNG

I will calculate the math to give the exact Running balance with Cents even if i have to modify the numbers, just to be exact with my stake balance.
I think graph is not updating results. will check many other things tomorrow.

its a lot of data entry to keep up with. I wouldn't be able to do this and play at the same time.

Hi, Mate
I can't keep up with the stats. I have an issue with the deposits please see workbook and transaction section,

The last deposit is suppose to be $35 I have enter the deposits just the way it shows on my staking. please see picture

44.PNG

I still need to update or enter deposits for $35, $10, $20, and $20
the last one in the workbook it supopose to be $35

I don't really have much time to enter now, I will be going to play a live tournament, bless me mate your the lucky charm
have a bless weekend!!! and bang one player made 1k in the staking

Mate I will reupload I enter the amounts. left spaces in between since maybe later tonight I will enter staking games that happen during the times.
I wrote the correct amount deposits in Column A rows 200, 203, 206, 208, and 210

As there is a possibility of multiple deposits per day then the date and time could be placed in the cells in the Date column of the Transaction table and the formula for the Deposit column on the Tournament sheet could be modified:

Formula:

Please Login or Register  to view this content.

Because C120:C122 are entered as text: June 26, 7:10
Change the values to 6/26/2023 7:10 instead

Thank you, Mate
will check now, im working on all the stats

I fixed that mate, Can you please tell me how I correct the format to be number instead of $ amount in column Rank, every time I create new rows?

I feel that we need to see an example of the Rank column displaying $ in a new row in order to help.

happy morning Mate,
I didn't finished the inputs, I can't get the numbers to be exact with my staking
will check once the buyins and prizes and see if i find the incorrect amt. and if not i will upload cheers

Mate, just check all the amt. and prizes. my running balance in the workbook is $7.74
my staking site is 0.4 cents not sure if there everything is calculating correct. who knows, Thats the reason why i re-enter everything and is still off.
and not even done with all the other stats will continued to work on them now.


Mate, please see changes
I added a new Catagory into bankroll column W11 Tournaments, can we get the formula I already have tournaments inputs in Tournament sheet.

Is final stage the same as FT?

Should cell Y12 display 17?
If so either change cell W11 to read Tournament, no 's', or change Tournament in the category column to Tournaments with an 's'.