Count when two people worked on the same day.

H

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I have the following data table and would like to know how many times two employees worked on the same. I tried a countifs formula but it doesn't work. Could anyone help me find a solution?

Cirugias.xlsx
ABCDE
1TechnicianType of SurgeryShiftDate
2RoseApendicectomia V10/22/20200
3MikeColecistectomia M/V10/23/20200
4RoseHemicolectomiaM10/24/20200
5MikeQuiste de ovarioM10/26/20200
6RoseLap. ExploradoraM10/26/20200
7MikeLap. ExploradoraM/V10/26/20200
8RoseLap. ExploradoraM10/27/20200
9MikeLap. ExploradoraM10/28/20200
10MikeLap. ExploradoraV10/30/20200
11RoseHisterectomiaV10/30/20200
12RoseHisterectomiaV10/30/20200
13MikeColecistectomia M/V10/31/20200
14RoseColecistectomia M11/4/20200
15MikeHisterectomiaM11/7/20200
16RoseQuiste de ovarioM9-Nov0
17RoseColecistectomia M11/11/20200
18MikeHisterectomiaM11/11/20200
19RoseLap. ExploradoraM/V11/12/20200
20RoseLap. ExploradoraM/V11/18/20200
21MikeApendicectomia V11/18/20200
22MikeHisterectomiaM11/19/20200
23RoseLap. ExploradoraM11/20/20200
24RoseColecistectomia M11/20/20200
25RoseApendicectomia V11/24/20200
26MikeHisterectomiaM11/25/20200
27MikeMiomectomia M/V11/25/20200
28RoseColecistectomia M/V11/25/20200
29RoseHisterectomiaM/v11/30/20200
30MikeHernia V12/3/20200
31RoseColecistectomia V12/5/20200
32MikeHernia M12/9/20200
33MikeColecistectomia M12/9/20200
34RoseApendicectomia V12/13/20200
35RoseHernia V12/16/20200
36MikeHernia M12/17/20200
37MikeColecistectomia V12/18/20200
38RoseApendicectomia V12/19/20200
39MikeMiomectomia M/V12/21/20200
40RoseLap. ExploradoraV1/1/20210
41RoseColecistectomia M1/16/20210
42MikeApendicectomia V1/19/20210
43RoseColecistectomia M1/20/20210
44RoseApendicectomia V1/20/20210
45MikeColecistectomia V1/23/20210
46RoseApendicectomia M1/24/20210
47MikeApendicectomia V1/25/20210
48RoseColecistectomia V1/28/20210
49MikeApendicectomia V1/30/20210
50MikeReseccion instetinalM1/30/20210
51RoseColecistectomia V2/1/20210
52MikeLap. ExploradoraV2/4/20210
53MikeColecistectomia M/V2/12/20210
54RoseHisterectomiaM/V2/19/20210
55MikeColecistectomia V2/19/20210
56MikeHisterectomiaM2/20/20210
57MikeColecistectomia V2/22/20210
58RoseColecistectomia V2/23/20210
59RoseLap. ExploradoraV2/24/20210
60RoseLap. ExploradoraM1/26/20210
61MikeApendicectomia V3/1/20210
Hoja1
Cell Formulas
RangeFormula
E2:E61E2=COUNTIFS($D$2:$D$61,D2,$A$2:$A$61,"Rose",$A$2:$A$61,"Mike")
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
The result that I should get, the number of times both employees work on the same day, is 10.
 
Upvote 0
Hello try this

In E2 and drag it down
Excel Formula: 
=COUNTIFS($D$2:$D$61,D2,$A$2:$A$61,"Rose")+COUNTIFS($D$2:$D$61,D2,$A$2:$A$61,"Mike")

In E62
Excel Formula: 
=COUNTIF(E2:E61,3)/3+COUNTIF(E2:E61,2)/2

Not sure if this is the right approach. AT least it is giving the desired result
 
Upvote 0
Hi,

May be this:

Book3.xlsx
ABCDE
1TechnicianType of SurgeryShiftDate
2RoseApendicectomia V10/22/20200
3MikeColecistectomia M/V10/23/20200
4RoseHemicolectomiaM10/24/20200
5MikeQuiste de ovarioM10/26/20201
6RoseLap. ExploradoraM10/26/20200
7Rose10/26/20200
8MikeLap. ExploradoraM/V10/26/20200
9RoseLap. ExploradoraM10/27/20200
10MikeLap. ExploradoraM10/28/20200
11MikeLap. ExploradoraV10/30/20201
12RoseHisterectomiaV10/30/20200
13RoseHisterectomiaV10/30/20200
14MikeColecistectomia M/V10/31/20200
15RoseColecistectomia M11/4/20200
16MikeHisterectomiaM11/7/20200
17RoseQuiste de ovarioM11/9/20200
18RoseColecistectomia M11/11/20201
19MikeHisterectomiaM11/11/20200
20RoseLap. ExploradoraM/V11/12/20200
21RoseLap. ExploradoraM/V11/18/20201
22MikeApendicectomia V11/18/20200
23MikeHisterectomiaM11/19/20200
24RoseLap. ExploradoraM11/20/20201
25RoseColecistectomia M11/20/20200
26RoseApendicectomia V11/24/20200
27MikeHisterectomiaM11/25/20201
28MikeMiomectomia M/V11/25/20200
29RoseColecistectomia M/V11/25/20200
30RoseHisterectomiaM/v11/30/20200
31MikeHernia V12/3/20200
32RoseColecistectomia V12/5/20200
33MikeHernia M12/9/20201
34MikeColecistectomia M12/9/20200
35RoseApendicectomia V12/13/20200
36RoseHernia V12/16/20200
37MikeHernia M12/17/20200
38MikeColecistectomia V12/18/20200
39RoseApendicectomia V12/19/20200
40MikeMiomectomia M/V12/21/20200
41RoseLap. ExploradoraV1/1/20210
42RoseColecistectomia M1/16/20210
43MikeApendicectomia V1/19/20210
44RoseColecistectomia M1/20/20211
45RoseApendicectomia V1/20/20210
46MikeColecistectomia V1/23/20210
47RoseApendicectomia M1/24/20210
48MikeApendicectomia V1/25/20210
49RoseColecistectomia V1/28/20210
50MikeApendicectomia V1/30/20211
51MikeReseccion instetinalM1/30/20210
52RoseColecistectomia V2/1/20210
53MikeLap. ExploradoraV2/4/20210
54MikeColecistectomia M/V2/12/20210
55RoseHisterectomiaM/V2/19/20211
56MikeColecistectomia V2/19/20210
57MikeHisterectomiaM2/20/20210
58MikeColecistectomia V2/22/20210
59RoseColecistectomia V2/23/20210
60RoseLap. ExploradoraV2/24/20210
61RoseLap. ExploradoraM1/26/20210
62MikeApendicectomia V3/1/20210
6310
Sheet813
Cell Formulas
RangeFormula
E2:E62E2=IF(SUM(COUNTIFS(D$2:D$62,D2,A$2:A$62,{"Rose","Mike"}))=1,0,IF(COUNTIF(D$2:D2,D2)=1,1,0))
E63E63=SUM(E2:E62)
 
Upvote 0
What does "how many times two employees worked on the same." mean the sentence seem to have been truncated ?
I initially thought it meant on how many days did both employees work. However this only gives a count of 6.
Based on what's coloured in yellow it also seems to count where the same person performed multiple types of surgery. eg Rose on 20 Jan, Mike on 30 Jan, Mike on 12 Dec, Rose on 20 Nov.

If it was really meant to be where both Rose and Mike worked on the same day AND you have office 365, then this will work.
(It gives the answer of 6)
Its not terribly scalable though ie it won't work for 3 employees.

PS: this is not using the helper column E and just calculating the result in one step, so just paste it into any unused cell.

Excel Formula: 
=COUNTA(UNIQUE(FILTER(A2:E61,{1,0,0,1,0})))/2-COUNTA(UNIQUE(D2:D61))
 
Last edited:
Upvote 0
Alex Blakenburg said:
What does "how many times two employees worked on the same." mean the sentence seem to have been truncated ?
I initially thought it meant on how many days did both employees work. However this only gives a count of 6.
Based on what's coloured in yellow it also seems to count where the same person performed multiple types of surgery. eg Rose on 20 Jan, Mike on 30 Jan, Mike on 12 Dec, Rose on 20 Nov.

If it was really meant to be where both Rose and Mike worked on the same day AND you have office 365, then this will work.
(It gives the answer of 6)
Its not terribly scalable though ie it won't work for 3 employees.

PS: this is not using the helper column E and just calculating the result in one step, so just paste it into any unused cell.

Excel Formula: 
=COUNTA(UNIQUE(FILTER(A2:E61,{1,0,0,1,0})))/2-COUNTA(UNIQUE(D2:D61))
Click to expand...
Thanks, you are completely right I missed that. I will try out the formula. Is there an alternate solution that is scalable and works for more than 3 employees?
 
Upvote 0
jtakw said:
Hi,

May be this:

Book3.xlsx
ABCDE
1TechnicianType of SurgeryShiftDate
2RoseApendicectomia V10/22/20200
3MikeColecistectomia M/V10/23/20200
4RoseHemicolectomiaM10/24/20200
5MikeQuiste de ovarioM10/26/20201
6RoseLap. ExploradoraM10/26/20200
7Rose10/26/20200
8MikeLap. ExploradoraM/V10/26/20200
9RoseLap. ExploradoraM10/27/20200
10MikeLap. ExploradoraM10/28/20200
11MikeLap. ExploradoraV10/30/20201
12RoseHisterectomiaV10/30/20200
13RoseHisterectomiaV10/30/20200
14MikeColecistectomia M/V10/31/20200
15RoseColecistectomia M11/4/20200
16MikeHisterectomiaM11/7/20200
17RoseQuiste de ovarioM11/9/20200
18RoseColecistectomia M11/11/20201
19MikeHisterectomiaM11/11/20200
20RoseLap. ExploradoraM/V11/12/20200
21RoseLap. ExploradoraM/V11/18/20201
22MikeApendicectomia V11/18/20200
23MikeHisterectomiaM11/19/20200
24RoseLap. ExploradoraM11/20/20201
25RoseColecistectomia M11/20/20200
26RoseApendicectomia V11/24/20200
27MikeHisterectomiaM11/25/20201
28MikeMiomectomia M/V11/25/20200
29RoseColecistectomia M/V11/25/20200
30RoseHisterectomiaM/v11/30/20200
31MikeHernia V12/3/20200
32RoseColecistectomia V12/5/20200
33MikeHernia M12/9/20201
34MikeColecistectomia M12/9/20200
35RoseApendicectomia V12/13/20200
36RoseHernia V12/16/20200
37MikeHernia M12/17/20200
38MikeColecistectomia V12/18/20200
39RoseApendicectomia V12/19/20200
40MikeMiomectomia M/V12/21/20200
41RoseLap. ExploradoraV1/1/20210
42RoseColecistectomia M1/16/20210
43MikeApendicectomia V1/19/20210
44RoseColecistectomia M1/20/20211
45RoseApendicectomia V1/20/20210
46MikeColecistectomia V1/23/20210
47RoseApendicectomia M1/24/20210
48MikeApendicectomia V1/25/20210
49RoseColecistectomia V1/28/20210
50MikeApendicectomia V1/30/20211
51MikeReseccion instetinalM1/30/20210
52RoseColecistectomia V2/1/20210
53MikeLap. ExploradoraV2/4/20210
54MikeColecistectomia M/V2/12/20210
55RoseHisterectomiaM/V2/19/20211
56MikeColecistectomia V2/19/20210
57MikeHisterectomiaM2/20/20210
58MikeColecistectomia V2/22/20210
59RoseColecistectomia V2/23/20210
60RoseLap. ExploradoraV2/24/20210
61RoseLap. ExploradoraM1/26/20210
62MikeApendicectomia V3/1/20210
6310
Sheet813
Cell Formulas
RangeFormula
E2:E62E2=IF(SUM(COUNTIFS(D$2:D$62,D2,A$2:A$62,{"Rose","Mike"}))=1,0,IF(COUNTIF(D$2:D2,D2)=1,1,0))
E63E63=SUM(E2:E62)
Click to expand...
Thanks. That seems to work for my original question. What is the logic behind the first part of the formula? I also don't understand why it's in braces, I tried substituting them for parenthesis and it doesn't work. I looked up braces in Excel and they say they that when you use arrays braces are added automatically, but even if I try using ctrl shift enter the parenthesis don't work.
 
Upvote 0
The braces inside the COUNTIF functions as an OR operator for the formula.
It can not be replaced by brackets(parenthesis), and does Not require CSE (control shift enter).
 
Upvote 0
heretolearnexcel said:
Thanks, you are completely right I missed that. I will try out the formula. Is there an alternate solution that is scalable and works for more than 3 employees?
Click to expand...

I can't come up with anything that is particularly elegant.

I have this time also included the helper column E.

I have added 2 names Steve and John into the data rows for testing.
There are 2 formulas at the bottom:
1st formula
Counts any day that has at least 2 of the technicians working on that day
2nd formula
Counts any day that has at least 2 of the technicians named in the formula working on that day

If you want more than 2 to be working ie in the 2nd formula you want all 3 to be working, then increase the >1 to >2 (for at least 4 make it >3)
Testing: I have added Steve to 2 of the days Rose AND Mike are working, so if you change either formula to >2, you will get a count of 2 being those 2 days.


20210302 Count non unique includes filter function v02.xlsx
ABCDE
1TechnicianType of SurgeryShiftDateAll Techs
2RoseApendicectomia V10/22/20200
3MikeColecistectomia M/V10/23/20200
4RoseHemicolectomiaM10/24/20200
5MikeQuiste de ovarioM10/26/20200.5
6RoseLap. ExploradoraM10/26/20201
7MikeLap. ExploradoraM/V10/26/20200.5
8RoseLap. ExploradoraM10/27/20200
9MikeLap. ExploradoraM10/28/20200
10MikeLap. ExploradoraV10/30/20201
11RoseHisterectomiaV10/30/20200.5
12RoseHisterectomiaV10/30/20200.5
13MikeColecistectomia M/V10/31/20200
14RoseColecistectomia M11/04/20200
15MikeHisterectomiaM11/07/20200
16RoseQuiste de ovarioM441440
17RoseColecistectomia M11/11/20201
18MikeHisterectomiaM11/11/20201
19RoseLap. ExploradoraM/V11/12/20200
20RoseLap. ExploradoraM/V11/18/20201
21MikeApendicectomia V11/18/20201
22MikeHisterectomiaM11/19/20200
23RoseLap. ExploradoraM11/20/20200.5
24RoseColecistectomia M11/20/20200.5
25RoseApendicectomia V11/24/20200
26MikeHisterectomiaM11/25/20200.5
27MikeMiomectomia M/V11/25/20200.5
28RoseColecistectomia M/V11/25/20201
29SteveColecistectomia M/V11/25/20201
30RoseHisterectomiaM/v11/30/20200
31MikeHernia V12/03/20200
32RoseColecistectomia V12/05/20200
33MikeHernia M12/09/20200.5
34MikeColecistectomia M12/09/20200.5
35RoseApendicectomia V12/13/20200
36RoseHernia V12/16/20200
37MikeHernia M12/17/20200
38MikeColecistectomia V12/18/20200
39RoseApendicectomia V12/19/20200
40MikeMiomectomia M/V12/21/20200
41RoseLap. ExploradoraV1/01/20210
42RoseColecistectomia M1/16/20210
43MikeApendicectomia V1/19/20210
44RoseColecistectomia M1/20/20210.5
45RoseApendicectomia V1/20/20210.5
46MikeColecistectomia V1/23/20210
47RoseApendicectomia M1/24/20210
48MikeApendicectomia V1/25/20210
49RoseColecistectomia V1/28/20210
50MikeApendicectomia V1/30/20210.5
51MikeReseccion instetinalM1/30/20210.5
52RoseColecistectomia V2/01/20210
53MikeLap. ExploradoraV2/04/20210
54MikeColecistectomia M/V2/12/20210
55RoseHisterectomiaM/V2/19/20211
56MikeColecistectomia V2/19/20211
57SteveColecistectomia V2/19/20211
58MikeHisterectomiaM2/20/20210
59MikeColecistectomia V2/22/20210
60RoseColecistectomia V2/23/20210
61RoseLap. ExploradoraV2/24/20210
62RoseLap. ExploradoraM1/26/20210
63MikeApendicectomia V3/01/20210
64SteveColecistectomia V2/21/20211
65JohnColecistectomia V2/21/20211
66
67
68
69
70Any combination minimum of 27
71You can make it more by changing the > 1 to > 2
72
73Any combination of itemised names minimum of 26
74You can make it more by changing the > 1 to > 2
My version of Col E
Cell Formulas
RangeFormula
E2:E65E2=IF(COUNTIFS($D$2:$D$67,$D2)>1,1/COUNTIFS($D$2:$D$67,$D2,$A$2:$A$67,$A2),0)
E70E70=COUNTA(UNIQUE(FILTER($D$2:$D$67, SUMIFS(E$2:E$67,$D$2:$D$67, $D$2:$D$67)>1, "No results")))
E73E73=COUNTA(UNIQUE(FILTER($D$2:$D$67, (SUMIFS(E$2:E$67,$D$2:$D$67, $D$2:$D$67,$A$2:$A$67,"Rose") +SUMIFS(E$2:E$67,$D$2:$D$67, $D$2:$D$67,$A$2:$A$67,"Mike") +SUMIFS(E$2:E$67,$D$2:$D$67, $D$2:$D$67,$A$2:$A$67,"Steve"))>1, "No results")))
 
Upvote 0
I think of Transpose (Unique(Filter (All the names, Condition = Date Analysed)) and then you concatenate them and remove duplicates (or use Unique again) - not a single step solution, nor super elegant, I guess, but might do the work?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
182
kerm007
K
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
269
amolvijay
A
N
  • Solved
Formula to get the range of certain Category
2 3
Replies
25
Views
391
Peter_SSs
Peter_SSs
S
  • Question
Check if a previously drawn number is in a group.
Replies
1
Views
280
Excel Novice 1
E
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
385
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,422
Messages
6,124,808
Members
449,191
Latest member
rscraig11

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top