Forum Discussion
Counting number of times the same entry has been entered in the previous 7 days.
Hi there,
I think you could use something like this...
=SUMPRODUCT(($C$3:C3="Player fee")*($B$3:B3>=INT((B3-1)/7)*7+1+TIME(6,0,0))*($B$3:B3<=INT(INT((B3-1)/7)*7+1+TIME(6,0,0))+7+TIME(5,59,59)))
There's basically 3 conditions here:
1. Check column C for "Player fee"
2. Check column B if the date is later than the previous Sunday at 06:00:00
3. Check column B if the date is prior than the following Sunday at 05:59:50
To find the previous Sunday, we use:
INT((B3-1)/7)*7+1+TIME(6,0,0))
To find the following Sunday, we use that calc with a slight variation:
INT(INT((B3-1)/7)*7+1+TIME(6,0,0))+7+TIME(5,59,59)
Note the above date checks include those times (less/greater than or equal to).
Enter formula in D3 and copy down. Since your data is transactional, it will count higher as data goes down. Meaning you don't know the exact end of your data set. If you did, you could plug that into the formula and all calculations would be for the same time period. This is more like a running total going down, so your data should be sorted ascending.
HTH
Zack BarresseI think I followed what you have there and it is clever. My question to you is why isn't the following Sunday calc just:
INT((B3-1)/7)*7+8+TIME(5,59,59)
that said aren't you missing that 0.0001% of the cases where it is > 5:59:59 but < 6:00:00 and instead you should just make the following sunday comparison < INT((B3-1)/7)*7+8+TIME(6,0,0)
@John74Racing Assuming Zack's comparisons are correct (they appear right on the surface except my comments) I think you could also use =countifs(....) with Zack's 3 comparisons. I think the result should be the same but my understanding is that sumproduct is an array formula that if used in high quantity could slow down the sheet.
BUT my real reason for replying is to ask you about that "auto timestamp" and how it works. you say the timestamp is in column B but the values in the formula are looking to see if it is 'A10'. I know row 10 is just the example row you picked but I don't get the column A nor do I get how it works exactly. I get that it is a circular reference and assume the trick is partly in that it is entered as an array formula (ctrl+shift+enter) and maybe has something to do with the convoluted comparison between CELL("address") and ADDRESS(ROW(A10),COLUMN(A10). But that said if A10 is blank then B10 is blank (OK good) but if A10<>"" then it falls into the next condition which is always false because B10<>A10 so it will always fall into the next condition which is always true because B10<>A10 which means it is assigned itself B10 but B10 is still "". I tried your sheet and couldn't get that timestamp to work for me. Just wondering what I'm missing
mtarler I am pretty green in excel and searched the formula. So the whole sheet is to manage an online poker game, a pretty large one at that. In column A we enter transaction values of Chips added, payments made, cashouts, and payer fees for every night they play. The time stamp is an important piece to mark what happened, when, with the formula I found it updates the timestamp if the enter is edited in column A. so if someone went and altered a payment from a few days ago it would timestamp today and i could see a discrepancy. so not sure if thats the best formula but it seems to be working well. I am more than willing to share the file with someone but i dont want to post it publicly.
- Hmm, yes, good catch on the formula time discrepancy.
Sumproduct can affect calculation speeds over many, many thousands of cells. If the workbook was going to grow that big it might be a concern, but I didn't get that impression from looking at it. If it were going to be a potential issue, I'd probably look at using Power Query instead of formulas tbh.
Also, the formula works because the OP set iterative calculations to be on, so circular references are allowed with a max iteration of 1. You can see this by going to File > Options > Formulas > Calculation options.Zack Barresse absolutely agree that sumproduct isn't a problem unless the sheet get very large. That said I just try to make it practice to use alternative if/when I can and this being a poker sheet with many players potentially going over many days, weeks, months... I don't know it may get rather large and it is much easier to start with the countifs now. Powerquery, that is a whole different beast 🙂
But again I digress as it is that auto time stamp formula that has me in a quagmier
Yes I see the circular ref but my bigger point was that it just didn't work when I tried it on that sheet nor does it make sense that it should work when I evaluated it. I tried variations on the formula and still couldn't get it to work either. Since it appears to work for the OP I'm still confuzzled why I don't get it.
