Forum Discussion
How to calculate how many days a person have worked from a list of transactions
Hi all,
I need to know how many dayes a person have been present at work by using some data from a list:
On the column A I have a list of transactions (sold an apple, sold an orange, sold something else and so on).
On the column B I have the name of the person who made that transaction.
On the column C I have the date (from previous ones til today/present).
Note: on one day I can find repeated multiple times the name of one person and the same date, depending on how much transactions that person have done... so that the rows of the column B, could be "Tom", "paul", "jack", "jack", "Carl", "Tom", "jack", etc and on the column C something like 01.02.23, 01.02.23, 01.02.23, 01.02.23, 02.02.2023, 02.02.2023, 03.03.2023.
Now: I need to know how many days Carl have worked (1), or Jack (3), or Simon (0). And it's sufficient that there is at least one transaction done by him for that day to determine that he has worked that day. If I find 0, he was not working and I can ignore that day to be counted.
Is it possible to do so by applying a formula based on that list?
Thank you
Let's say your data are in A2:C800.
With a name such as Tom in E2, enter the following formula in F2:
=IF(COUNTIF($B$2:$B$800,E2)=0,0,COUNTA(UNIQUE(FILTER($C$2:$C$800,$B$2:$B$800=E2))))
This can be filled down.
(It returns 2 for Jack in your example: Jack worked on 01.02 and 03.02)
3 Replies
Vesperwindمشكل في جداول كامل
في اللوان وفي كتابه وفي تنسيق
Let's say your data are in A2:C800.
With a name such as Tom in E2, enter the following formula in F2:
=IF(COUNTIF($B$2:$B$800,E2)=0,0,COUNTA(UNIQUE(FILTER($C$2:$C$800,$B$2:$B$800=E2))))
This can be filled down.
(It returns 2 for Jack in your example: Jack worked on 01.02 and 03.02)
