Counting number of times the same entry has been entered in the previous 7 days.

Copper Contributor

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: 

=IF(A10<>"",IF(AND(B10<>"",CELL("address")=ADDRESS(ROW(A10),COLUMN(A10))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A10),COLUMN(A10)),B10,NOW())),"")

 

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. 

 

 

9 Replies

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.

@John74Racing 

@John74Racing 

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 

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.

@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. 

@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!

encase the formula with and if statement
=if(C3="","",sumproduct(…) )

@John74Racing 

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:

https://exceljet.net/custom-number-formats

https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/