Forum Discussion
Solved
Count of Dates based on GUI, Transaction Date and Period Week
Hi All,
I am looking to calculate Week Count based on Employee GUI, Week Ending date and Period week
see below screenshot, where i need a formula to calculate that, any help on this is highly appreciate. Column N is what i did manually which is basically the output
may be count if can do this, not sure, appreciate any help on this
As variant
with
=MIN(1,COUNTIFS($A:$A,$A2,$L:$L,$L2)/5)*(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)
3 Replies
Not sure I understood the logic correctly, as variant that could be
with
=--(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)drag it down
- Thank you for this, additionally the Logic should also needs to be
based on Transaction date as well. for eg what i am trying to do is to calculate the number of weeks a person(Employee GUI) has worked. so in a week there could be 5 or 6 or 7 transaction date, count that as 1 and if its less thn 5 or 6 or 7 transaction date (for Eg in a week if there is only 4 transaction date, than count that as 4/5week which is 0.8 Week and so on for anything less thn 5 or 6 or 7 transaction date in that week.
I Hope i am not confusing you, this could be little hard to create not sureAs variant
with
=MIN(1,COUNTIFS($A:$A,$A2,$L:$L,$L2)/5)*(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)
