SOLVED

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

Copper Contributor

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

3 Replies

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

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

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

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 (Copper Contributor)
Solution

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

As variant

with

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

Accepted Solutions
best response confirmed by vjnvinod (Copper Contributor)
Solution

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

As variant

with

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