04-21-2020 12:29 PM - edited 04-21-2020 12:34 PM
04-21-2020 12:29 PM - edited 04-21-2020 12:34 PM
Looking for a function that can count the number of times the same value entry has been entered in the previous 7 days. Or even better would be from Sunday at 6:00am to Sunday 5:59am. We have a transaction list that has money in and money out, there are player fees that are added per night of play, but the fee is capped at a certain number.
We enter our transactions with an auto timestamp using:
I would like to have a cell that shows how many times player fees have been added during the weekly window.
A3 starts a transaction dollar, B3 Timestamp using the function above, C3 Memo
I would place the formula in D3.
04-21-2020 01:21 PM - last edited on 04-22-2020 09:47 AM by Eric Starker
If you just want to count , how many times the player fee has been added, a simple COUNTIF can be used.
=countif(cell C3, "="PLAYER FEE")
tHE OUTPUT WILL BE 1 OR 0. iT CAN BE COUNTED.
IF YOU WANT CUMULATIVE SUM, MAKE ONE MORE COLUMN AND ADD THE UPPER CELL WITH THE CELL TO THE LEFT.
I hope it helps.
04-21-2020 06:52 PM
I think you could use something like this...
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:
To find the following Sunday, we use that calc with a slight variation:
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.
04-21-2020 08:29 PM
@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:
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
04-21-2020 09:20 PM
04-21-2020 09:43 PM
@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.
04-22-2020 09:06 AM
@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.
04-22-2020 09:09 AM
@Zack Barresse This is great! Is there a function we could add so that if there is no data in column C the entry appears blank?
thank you so much!
04-22-2020 09:34 AM
04-22-2020 09:44 AM
You can use an IF statement, as @mtarler showed, or you could alter the format (my preference). Why? It's about downstream calculations. While a simple =SomeCell will treat "" as a zero, as well as =SUM(SomeCell), my preference is to have an actual zero value if that fields purpose is numerical. If I don't want to show the zero value, I'll adjust the format accordingly. It can get tricky, but cell formatting (i.e. Custom) works like this:
Greate than zero; Less than zero; Equal to zero; Text
So if you look at the Comma style format, it looks like this:
_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
You'll notice each part separated by a semi-colon, along with some special characters which represents how you want to show your numbers. If you leave that section blank, nothing is shown for the format, although the value still resides in the cell.
A fun trick to play on co-workers is to format the cell like: ;;;
This will not show any values. Muwahaha! Don't tell them I told you that lol.
Some great formatting resources: