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 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!
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; TextSo 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/
- encase the formula with and if statement
=if(C3="","",sumproduct(…) )
