SOLVED

Count of Dates based on GUI, Transaction Date and Period Week

New Contributor

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

3 Replies

@vjnvinod 

Not sure I understood the logic correctly, as variant that could be

image.png

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 sure

best response confirmed by vjnvinod (New Contributor)
Solution

@vjnvinod 

As variant

image.png

with

=MIN(1,COUNTIFS($A:$A,$A2,$L:$L,$L2)/5)*(COUNTIFS($A$2:$A2,$A2,$L$2:$L2,$L2)=1)