help with Bankroll stats from different sheets to be calculated

What you are describing as an end time sounds like a text entry, which Excel will not be able to use in a calculation.
If you can attach a sample file showing the start and end times then there might be a work around, but I would not get my hopes up.

oh okay cool thanks mate will upload i input in row 8 in pokerstake sheet start 10/3/2023 11:30:00 AM end time Nov 29, 19:06

Added another column End Time 2 (AV) which can be populated using: =SUM(DATE(2023,MATCH(LEFT([@[End Time]],3),Info!AE$2:AE$13,0),MID([@[End Time]],5,2)),RIGHT([@[End Time]],5))
Hours column (I) can be populated using: =MOD([@[End Time 2]]-[@Start],1)

thank you, will see how it works
nice i will try to make the changes in the latest workbook

this is the most up to date file i added the start end time

mate, this is the most up to date file

Here is the file with the formula from post #205 pasted back into cell AV8.
Also there is a list of months in cells AE2:AE13 on the Info sheet.

thank you will try to update latest file in post #208 once i catch up ill re-upload

about to upload,

YOu think we can use the index column in pokerstake sheet to replace #sess? it keeps skipping ss.png
can we get the More ressults formulas, would like to check what neeed to be done in poketstake after that and separate file from the whole workbook

Formula in the #sess column can be changed to: =SUM(INDEX(tbl_PokerStake[[#All],['# Sess]],ROW()-6),1)
Index column can be deleted by right clicking a cell in the column and select Delete > Table Columns
Manually type some amount, without the dollar sign, into cell N279.
Note that cell N279 will display the green triangle in the top left corner but it doesn't mean anything. In a previous post in this thread is a link to an article stating how to remove the triangle if it bothers you.
Select cell AU279 and press the Tab key.
Cell N280 should have the correct formula i.e. IF([@Tournaments]="","",ROUND([@Share]*[@[%*MU]],2))

Do you mean that you want deposits excluded from the #sess count?
By preset, do you mean you want to fill those columns with dropdowns?
Please attach the most recent version of the workbook.

yes please want only # sess count, by preset i would like the column N to produce the 3 for when choosing the game in column game
example i pick spin & go from dropdown in m31 and to produce the 3 in n31 and the site will upload

note: that i have a:d columns hide in pokerstake cash maybe bankroll

For the #sess column: =COUNTIFS(INDEX([Tournaments],1):[@Tournaments],"<>")
#sess column is conditionally formatted: =H7="" (font = black)
For the entrants column: =IF(OR(M30="Spin & Go ",M30="Flash Hold'em ",M30="Jackpots",M30="SPINS Overdrive"),3,"")

thank you, the Don sng it produces correct the entrants but not the site

We need a fuller explanation of what is expected.

okay to show the site is always going to be the same session 2 pokerstars session 3 pokerstars, sesion 5 ACR, and session 6 partypoker

image upload not working i think
can you make pokerstake sheet regroup as is getting long

I don't see anything that says session on the DoN SnG sheet. However, if you want Site to be decided by Entrants: =IF(OR([@Entrants]=2,[@Entrants]=3),"PokerStars",IF([@Entrants]=5,"ARC",IF([@Entrants]=6,"partypoker","")))
I don't know what is meant by "can you make pokerstake sheet regroup".

The tbl_PokerStake table is already sorted by start date.
Grouping is a feature of pivot tables not Excel tables.
If you are looking for a fast way to get to the last row press the Ctrl and down arrow keys.

Select the sort/filter button at the right side of cell E6.

That will produce a circular reference if the Site column is supposed to be populated from the value in the Entrants column and now you want the Entrants column to be populated based on the Site.
If this isn't what you want, then please start over with the explanation as I am lost as a goose.

On the DoN SnG sheet try pasting the following into cell X2 and then drag the fill handle down to cell W26: =IFERROR(AGGREGATE(14,6,tbl_DonSnG[Profit/Loss]/(tbl_DonSnG[Profit/Loss]<>""),ROWS(W$2:W2)),"")
Select cell W27 and press the Alt and = keys and then press the Enter key.

In Y2 and down: =IF(W2="","",INDEX(tbl_DonSnG[Category],AGGREGATE(15,6,(ROW(tbl_DonSnG[Category])-ROW(tbl_DonSnG[#Headers]))/(tbl_DonSnG[Profit/Loss]=W2),COUNTIFS(W$2:W2,W2))))

thank you, wait for a few minutes please till i do applied this formula and reupload latest file since i organize the catagories and remove some things change a bit not affecting formulas i think will try to just adjust the column name of this formula thank you, never mindd you beat me to the punch

okay i added column for site to show results for spins separate to sng
from session #1 I organize the corresponding site category game column and entrants for sit & gos it varies entrants could be from
8, 18, 24, 36, 18-45 latest file

I am still flying blind as to what you want done.
I put formulas in columns AB and AD but not at all sure that is what is wanted.

okay in column ab i want the largest wins for spin and gos to show the site and on column ad to show site for sit & gos only 2 different type of tournaments like tournaments & cash see in this picture we have both tournaments and cashAttachment 851268

I rename sng to Sit & Gos maybe thats why you think is the same game my badtour.png

For spins: =IFERROR(AGGREGATE(14,6,tbl_DonSnG[Profit/Loss]/(tbl_DonSnG[Profit/Loss]<>"")/(tbl_DonSnG[Game]<>"Sit & Go"),ROWS(AA$2:AA2)),"")
For sit & go: =IFERROR(AGGREGATE(14,6,tbl_DonSnG[Profit/Loss]/(tbl_DonSnG[Profit/Loss]<>"")/(tbl_DonSnG[Game]="Sit & Go"),ROWS(AA$2:AA2)),"")

have a question, all last night took me to figure this =(O28+P28)-(V28) for u28 column in don, could there be a better formula for sit and gos, as this one with out the other one to stop working
the spins formula is using this formula =VLOOKUP(N27,Info!$L$45:$M$56,2)*Q27-T27*N27
but for sit and gos can the other be set for when sit & go is pick

trying to fix the categories trying to put 2 and 2 together using formula from spins coloum b in don to use in played for categories not sur if this formula i made is good for that
=COUNT(SUMIFS(tbl_DonSnG[Category],tbl_DonSnG[Site],A17)) don't think is right is not adding up please help with all categories stats

Try the following in cells U22 and down on the DoN SnG sheet: =IF(L22="Sit & Go",(O22+P22)-(V22),VLOOKUP(N22,Info!$L$45:$M$56,2)*Q22-T22*N22)
Played: =COUNTIFS(tbl_DonSnG[Category],V4)

fantastic you you nailed it
don't want you flying blind
working on played

I filled in all categories by copying and pasting the formulas from the spin & gold section, but I encourage you to look at the formulas and to check that the formulas are working as expected.
In the spin & gold section, formula for ITM%, I am just guessing that what was called cashes on the Cash sheet is the same as profit/loss on the DoN SnG sheet.
For the Acr Sit & Go section is that supposed to be combining the site and category names or will there later be a category named Acr Sit & Go?

thank you, i will see all changes to give you a more accurate answer
yes is the same cashes/profit/loss on the don sng sheet, i will check the numbers and %
for the acr great questions sorry i din't mention that Jackpots is the spin & gos a bit of different name in column L:m acr/jackpots for spin & go

Fixed using: =SUM(SUMIFS(tbl_DonSnG[Wins],tbl_DonSnG[Category],V4),SUMIFS(tbl_DonSnG[Loses],tbl_DonSnG[Category],V4))

thank you will make the corrections is it possible to to the 3 to 6 and 3 to 8
from post# 249
would the formula for the sit & Go be the same as spin & Go
=SUM(SUMIFS(tbl_DonSnG[Wins],tbl_DonSnG[Category],N12),SUMIFS(tbl_DonSnG[Loses],tbl_DonSnG[Category],N12))

Paste the following into cell M22 and copy down: =IF(OR(L22="Spin & Go ",L22="Flash Hold'em ",L22="Jackpots",L22="SPINS Overdrive"),3,IF(L22="Spin & Gold ","3 to 6",IF(L22="Max Hold'em","3 to 8","")))
The formula listed in post #255 is already in the file attached to post #254 under the SPINS Overdrive section.
There are three sit & go sections. Will there be category names that include GG, pokerstars and partypolker?

thank you, i will check all changes, for question on the 3 sections for sit & go i would like the category to be sit & go and maybe just have the site name with a header name of sit & gossit.png like that maybe

here is the layout for sit & gomm.png

can we revised formula for 3 to 6, 3 to 8
=IF(OR(L22="Spin & Go ",L22="Flash Hold'em ",L22="Jackpots",L22="SPINS Overdrive"),3,IF(L22="Spin & Gold ","3 to 6",IF(L22="Max Hold'em","3 to 8","")))
okay i don't think i explain myself correct, what i want it to do is to enter amount in entrants column from 3, 4, 5, 6 depending what the system draw generates if the draw is 4 players to decide the game Spin & gold, and if possible to decide the site GG
same for 3 to 8
3, 4, 5, 6, 7, 8 draw is 6 produce game Maxxx Hold'em and pokerstars and it will be all set once i input the # of entrants

Formulas for the Sit & Go are applied.
Still don't understand the last part.

nice thank you.

for 3 to 6 i want to input the # of entrants in entrants columns if i choose gg and pokerstars the sites with 3 to 6 and 3 to 8
GG spin & gold when i enter the amt. of entrants from 3 to 6 let say 5 is the draw of players i put in 5 in entrats column and that decides the category spin & gold
same for spin & go max for 3 to 8 players

Are the number of entrants going to be manually placed into the cells in column M or are they going to be based on the game in column L?

Should we assume that the games in column L will be entered using data validation as in the file attached to post #259?

In the file attached to post #259 cells L25:L38 each have drop downs which are linked to =Info!$V$2:$V$8 by data validation.

Do you have a list of which sites and categories correspond to which games?

I don't see any lists on the DoN SnG sheet so I used what is already in columns H, I and L to make lists in columns AQ:AS
Populated the Site column using: =INDEX(AR$2:AR$7,MATCH([@Game],AQ$2:AQ$7,0))
Populated the category column using: =INDEX(AS$2:AS$7,MATCH([@Game],AQ$2:AQ$7,0))
Recommend that you add any other games, sites and categories in columns AQ:AS.
Recommend that you delete the formula from the Entrants column and fill that manually.
Have a blessed night.

mate, can we get fixed the formulas for request in post #200 in pokerstake
this is the latest file
i would like the total: to be consistent from the beginning of the year, and show either profit or loss above the heading
if you see file in post#200 i input all the full year in pokerstake to reflect on more results i din't copy paste in this file because when i do it it show Ref# in columns e and f because we modify the start and end time, but not in file in post 200 it dosen't show ref# because still has the old fime format.

i attached file here from post #200

Remove the #REF from formula in cell C2 so that it reads: =SUM(C5:C35)
All the rest of the totals should then display.

thank you would it be the same for tournaments results? how about for totaL in R1 what would formula be ? =aj2 ?

The formula in cell AD2 is not correct, change to read: =SUM(AD5:AD35,AA2)
For cell R1: =AJ2
Don't see a corresponding Totals row on the Results sheet, please upload the latest file with next post.
Largest cash wins: =IFERROR(LARGE(tbl_Cash[Profit],ROWS(AH$2:AH2)),"")

it was the total profit: =J2 i made all changes, latest file

can we adjust the bankroll challenge section in don sNg ban.jpg

i also get error#n/a for sit & go row 38 sit and gos are going to be from all sites not sure how to update that aQ:as columns

Need to define Cashes as there is no column on the DoN SnG sheet.
Rest done, I think.

thank you, but im not sure exactly what i need to do, do i create column with name cashes?
for site category not sure what i need to do for #n/a to be fixed when choosing sit & go since sit & go would be all sites except GG since they don't run sit & Gos

not sure what is the formulas for H22 & i22 are for also winrate column show #N/A

in row 29, shows #n/a when sit & Go picked
i try these
=INDEX(AR$2:AR$8,MATCH([@Game],AR$2:AR$8,0))
=INDEX(H$23:H$50,MATCH([@Site],AQ$2:AQ$8,0))
and other things i can fixed it

"do i create column with name cashes?" > Don't have to, but we do need to know how you would manually calculate cell H9.
"when choosing sit & go since sit & go would be all sites except GG since they don't run sit & Gos" > may have to fill those in manually or designate each sit & go as in ps sit & go for Pokerstars sit & go.
"I found a glitch in column U which hope there is a fix for" > Which cells and what do you expect to see in them?

Fixed H6:H11 again.
Cashes: =SUMIFS(tbl_DonSnG[Profit/Loss],tbl_DonSnG[Profit/Loss],">0")
Category: =INDEX(tbl_GameSiteCategory[Category],AGGREGATE(15,6,(ROW(tbl_GameSiteCategory[Game])-ROW(tbl_GameSiteCategory[#Headers]))/(tbl_GameSiteCategory[Game]=[@Game])/(tbl_GameSiteCategory[Site]=[@Site]),1))
You need to put actual categories in cells AS8:AS11
H29 and H38 are manually entered.

I would like to finished the stats left starting from bankroll sheet finish positions single table and winning % which will also complete Dons sng as we need single table SNG
finish.jpg
you can see the original file as sample if needed

The formula from the results file would translate into:
For count: =SUMPRODUCT((tbl_Tournaments[Rank]=ROWS(AC$11:AC11))*(ISNUMBER(MATCH(tbl_Tournaments[Entrants],{9,10},0))))
For average: =IFERROR(AC11/SUMPRODUCT(--ISNUMBER(MATCH(tbl_Tournaments[Entrants],{9,10},0))),0)
Note that the formulas only count/average those games where there are either 9 or 10 entrants as per the results file.

Thank you, would it be the same for don sng?

Yes, although the table name will be different and the DoN SnG sheet does not have a 'Rank' column.

okay i will do that the Don sng sheet has a place which i cana rename to rank maybe that works?
how can we correct the #N/A in row 7 and 16 in don sheet?west.jpg


i reaname column now it shows like that

If you mean H29:I29 and H38:I38 then look back at post #286.
The file attached to that post has a table in columns AQ:AS.
You need to put actual categories in cells AS8:AS11 as stated in that post.

IM sorry i stay behind on tha post not sure how i miss it, i am adding the categories
can i just copy the table to the latest file

not sure if i can use the same name category sit & Go as it shows here, if it would return trouble with fomula and sitescat.jpg

I think is working, just want to make sure that if for sit & gos i have to manually enter the site?
because when i do I column returns #Num! here you can see how it work in the latest file
im in a battle with the file right now give me few minutes trying to put the puzzle together from file in post#286 and file in post# 288

can we continued with other stats
okay this is the latest file i have question about column H and entering the site manually for sit & go, not sure if thats the best way to have it set up or if there is a better way without having the formula deleted when entering the site for the sit and go?
or you think it was best to start with site with a dropdown list rather than in the game? not sure your the expeert not sure if there is a workaround that

Removed extra space from Blast in cell V9 on Info sheet
Again applied formula to Site column: =INDEX(tbl_GameSiteCategory[Site],MATCH([@Game],tbl_GameSiteCategory[Game],0))
Category: =INDEX(tbl_GameSiteCategory[Category],MATCH([@Game],tbl_GameSiteCategory[Game],0))
The above formulas use structured references so they will adjust as items are added to tbl_GameSiteCategory.
Looking at tbl_GameSiteCategory it seems that if the game is sit & go that the site will also be sit & go, so I think leaving the drop down in the game column will work.

thank you, for sit & go do i manually enter the site? because after picking sit & go from the game column L pokerstars shows in site?
but when entering lets say 888poker in row 38 it deletes the formula in site column

can you advice as how to fixed the bankroll callenge sections don't know what to do i know you said to define the name but how do i go about tha or what would be the best to do, as i think thats causing the #n/a on column winrate

It only overwrote the formula in cell H38. If you add another row to the table you'll see that the formula is in cell H39.
It seems like the win rate column should have a formula like: =[@Wins]/SUM(tbl_DonSnG[@[Wins]:[Loses]])

after testing column s and u in don comparing to Results_file only stat that don't match is row 10 for sng 5-9 you can see in the picturemm.png

the last one one show 8% in don and in results 38%
its because in don is only counting 1 and in results 5
if you can see cell x29 shows 0.00% not sure if something can be done since i input this formula
=[@Wins]/SUM(tbl_DonSnG[@[Wins]:[Loses]])

For cell T10 on the DoN SnG sheet: =SUMPRODUCT((tbl_DonSnG[Rank]>=ROWS(T$11:T15))*(ISNUMBER(MATCH(tbl_DonSnG[Entrants],{9,10},0))))
What should be in cell X29 on the DoN SnG sheet? I would assume that since there is a prize in cell P29 that there should be a win (at least) in cell Q29.
Please specify the sheet to which you are referring in post #303.

its working now but when you click on the bar where the cell is you you see the red square going down seems

x29 shoould be the winrate %

Which is the number of wins divided by the sum of wins and losses. There are no wins recorded in cell Q29 so X29 displays 0.00%. However, cell P29 displays a $1000 prize so it seems to me that at least one win should have been displayed in cell Q29.
I changed the formatting in the Site column to: General;;
Cell U11: =COUNTIFS(tbl_DonSnG[Game],"Satellites",tbl_DonSnG[Rank],1)

have a question? since i added the satellites how can that be set to work as sit & go without makig other formulas stop working as they are now
when i add the prize of 115 it shows 5 in profit/loss

Mate, can you help with the highlighted sections in don sng to original file i did as much to match and toubleshoot how categories work, fixed many little issues and arrange names I couldn't figure out the sit & Go sections as my eyes feel tire when my vision starts going blurry must stop. I would like to add the satellites to work same as sit & gos, i have to use the link to the original don file since when i download it gets corrupted or formulas stop working so i made copy and save to my drive as I was comparing with the workbook stats
this is the link to the Don original file from my google drive.
https://docs.google.com/spreadsheets...it?usp=sharing
Session 17 would be the Satellites

L5:L11 and N5:N11 sections are now working.
Note that I had already rewritten the formulas for Sit & Go in an earlier post, please be careful to keep them.
I didn't see any expected result for SNG Head-Up Win % so I didn't attempt to guess at what that should be.

only this section from original don and workbook not matching after adding wins tables add in don
and in workbook stays at 2 ee.jpg
https://docs.google.com/spreadsheets...usp=drive_link

why did you highlight these sections can i delete all or leave 1 to get stats?
stats.jpg
like this sit & go still gives results ? kkk.jpg

can these be deleted too? Screenshot.jpg

Okay i see they connected to the tablegamesite after doing a trace just don't like how they show right infront

Cell R50 on the Info sheet should be: =ROUNDDOWN(R49/Q46,0)
I had highlighted cells AQ9:AQ11 and AS9:AS11 because most of them had been changed from Sit & Go to Sit n Go which isn't the designation on cell P12 so it was hampering the calculations. You could delete AQ9:AS10 as you'll have to manually input categories.
You can delete the formulas in cells H22:I22.

Deleted the tbl_GameSiteCategory rows that had Sit & Go in the game column and blanks in the site column.
Assuming that you mean fix a satellite section to be like P12:Y20, where are you planning to put it?

thank you, Okay i witch blast sit & go down and satellites up since 888poker don't have spin & go only sit & go which is the same as spin and go they for some how name blast sin & go but are actually spinssat.jpg
i didn't detele th sit & go it remains like this Screenshot 2023-12-06 192506.jpg otherwise give error if i delete

please fixed the tournament sheet column G for hours to be same as pokerstake i can't fix it

Looks like everything is good with satellites and blast sit & go, but would be easier to say if there was an updated file.
Leave that row. You'll have to manually fill in the site if it is other than Acr.
As for getting hours (G) to work on the Tournaments sheet it is now set up same as PokerStake sheet so that you can put dates like Nov 29, 15:40 in column F.

thank you mate,after testing and comparing formula for si n go & satellites seems exact the same but only giving out different amt. in profilt/loss column in Sess 16 i input buyIn of 15 and prize of 75 sit n go returnes 60 in u column but satellite only 5 dollars
i do see it show "sit & go" on both i try changing name in formula to "satellite"

okay i fixed it is " satellites"
it shouldn't be no trouble right with sit & go and satellites

only issue i see not is when adding a new row and changing from sit & go to satellite the formula remain the name sit & go ? in the formula
and column AA stops producing and tournaments sheet column G showing #n/A

Please upload the file.

I think that this addresses the issues from post #324 that are on the DoN SnG sheet.
As to column G on the Tournaments sheet, once you put in date and time in column F (as in cell F8) then the #N/A's should resolve.