Hello All
i wan to know that the number of days that someone worked in, for example in this example below
John Doe worked in two different day so the number of days is 2, so how to write a function for this purpose please?
i have attached a sample data,
Regards
how to count the unique days some one worked in?
-
- BronzeLounger
- Posts: 1227
- Joined: 01 May 2016, 09:58
how to count the unique days some one worked in?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how to count the unique days some one worked in?
In M7 as an array formula confirmed with Ctrl+Shift+Enter:
=SUM(--(FREQUENCY(IF(($B$2:$B$15=K7)+($C$2:$C$15=K7),MATCH($A$2:$A$15,$A$2:$A$15,0)),ROW($A$2:$A$15)-ROW($A$2)+1)>0))
Fill down.
Remark: Mark Polo worked on both dates, so the formula in M9 returns 2, not 1.
=SUM(--(FREQUENCY(IF(($B$2:$B$15=K7)+($C$2:$C$15=K7),MATCH($A$2:$A$15,$A$2:$A$15,0)),ROW($A$2:$A$15)-ROW($A$2)+1)>0))
Fill down.
Remark: Mark Polo worked on both dates, so the formula in M9 returns 2, not 1.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1227
- Joined: 01 May 2016, 09:58
Re: how to count the unique days some one worked in?
Thanks a lot, working very good ...
-
- 2StarLounger
- Posts: 150
- Joined: 11 Jun 2012, 20:37
Re: how to count the unique days some one worked in?
In the attached, if you right-click the table at cell I19 and choose Refresh the table will update itself. It will cope without change if you add more name columns or date rows to the table at top-left of the sheet.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1227
- Joined: 01 May 2016, 09:58
Re: how to count the unique days some one worked in?
thanks a lot, it is very useful
Regards