I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. I've tried several
variations of countif to no avail.

Thanks,
mb

Try this:

B1:F1 = column headers
A2:A7 = row headers
B2:F7 = schedule matrix

A12 = first employee name
B12 = second employee name

=SUMPRODUCT(--(INDEX(B2:F7,MATCH(A12,A2:A7,0),)=INDEX(B2:F7,MATC H(B12,A2:A7,0),)))

--
Biff
Microsoft Excel MVP


"michaelberrier" wrote in message
...
I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. I've tried several
variations of countif to no avail.

Thanks,
mb

On Jan 30, 5:36*pm, "T. Valko" wrote:
Try this:

B1:F1 = column headers
A2:A7 = row headers
B2:F7 = schedule matrix

A12 = first employee name
B12 = second employee name

=SUMPRODUCT(--(INDEX(B2:F7,MATCH(A12,A2:A7,0),)=INDEX(B2:F7,MATC H(B12,A2:A7,0),)))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...

I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. *The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. *So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. *I've tried several
variations of countif to no avail.

Thanks,
mb

Biff,

In your example you have the employee names listed outside of the list
of row headers which I think is fouling up the works. Here is the
actual arrangement of my schedule:
B6:AQ6 -- Column Headers (dates)
A7:A14 -- Row Headers (Names)
B7:AQ14 -- Schedule Matrix

First employee name is in A7, and there are 8 employees total. I am
trying to input a formula that matches each of the bottom 7 employees'
schedule with the first one, so at the end of the row containing each
employee's schedule will be a count of how many shifts each shares
with employee #1 (A7). Based on your example, here is the formula I
wrote to compare the #2 employee (A8) with #1, which returns an #N/A
error:

=SUMPRODUCT(--(INDEX(B7:AQ14,MATCH(A7,A7:A14,0)=INDEX(B7:AQ14,MA TCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

Try this...

Entered in AR8 and copied down to AR14:

=SUMPRODUCT(--(B$7:AQ$7=B8:AQ8))

--
Biff
Microsoft Excel MVP


"michaelberrier" wrote in message
...
On Jan 30, 5:36 pm, "T. Valko" wrote:
Try this:

B1:F1 = column headers
A2:A7 = row headers
B2:F7 = schedule matrix

A12 = first employee name
B12 = second employee name

=SUMPRODUCT(--(INDEX(B2:F7,MATCH(A12,A2:A7,0),)=INDEX(B2:F7,MATC H(B12,A2:A7,0),)))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...

I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. I've tried several
variations of countif to no avail.

Thanks,
mb

Biff,

In your example you have the employee names listed outside of the list
of row headers which I think is fouling up the works. Here is the
actual arrangement of my schedule:
B6:AQ6 -- Column Headers (dates)
A7:A14 -- Row Headers (Names)
B7:AQ14 -- Schedule Matrix

First employee name is in A7, and there are 8 employees total. I am
trying to input a formula that matches each of the bottom 7 employees'
schedule with the first one, so at the end of the row containing each
employee's schedule will be a count of how many shifts each shares
with employee #1 (A7). Based on your example, here is the formula I
wrote to compare the #2 employee (A8) with #1, which returns an #N/A
error:

=SUMPRODUCT(--(INDEX(B7:AQ14,MATCH(A7,A7:A14,0)=INDEX(B7:AQ14,MA TCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

On Feb 2, 1:02*pm, "T. Valko" wrote:
Try this...

Entered in AR8 and copied down to AR14:

=SUMPRODUCT(--(B$7:AQ$7=B8:AQ8))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...
On Jan 30, 5:36 pm, "T. Valko" wrote:



Try this:

B1:F1 = column headers
A2:A7 = row headers
B2:F7 = schedule matrix

A12 = first employee name
B12 = second employee name

=SUMPRODUCT(--(INDEX(B2:F7,MATCH(A12,A2:A7,0),)=INDEX(B2:F7,MATC H(B12,A2:A7,0),)))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

....

I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. I've tried several
variations of countif to no avail.

Thanks,
mb

Biff,

In your example you have the employee names listed outside of the list
of row headers which I think is fouling up the works. *Here is the
actual arrangement of my schedule:
B6:AQ6 -- Column Headers (dates)
A7:A14 -- Row Headers (Names)
B7:AQ14 -- Schedule Matrix

First employee name is in A7, and there are 8 employees total. *I am
trying to input a formula that matches each of the bottom 7 employees'
schedule with the first one, so at the end of the row containing each
employee's schedule will be a count of how many shifts each shares
with employee #1 (A7). *Based on your example, here is the formula I
wrote to compare the #2 employee (A8) with #1, which returns an #N/A
error:

=SUMPRODUCT(--(INDEX(B7:AQ14,MATCH(A7,A7:A14,0)=INDEX(B7:AQ14,MA TCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

Hmmmm....

That returns an 11 when the number of common dates between the two is
5. Not sure where that number comes from as the two don't have 11 of
anything in common that I can tell.

I can't imagine I'm the first person that's tried to do this, but I
can't find anything of the sort in countless fruitless searches.

Thanks for helping!
mb

Here's my understanding of what you want to do:

................Date1.....Date2.....Date3.....form ula
Name1......A............B............C............ ........
Name2......C............B............A............ 1......
Name3......A............B............C............ 3......

Name2 has 1 common entry with Name1.
Name3 has 3 common entries with Name1.

Is that what you wanted to do?


--
Biff
Microsoft Excel MVP


"michaelberrier" wrote in message
...
On Feb 2, 1:02 pm, "T. Valko" wrote:
Try this...

Entered in AR8 and copied down to AR14:

=SUMPRODUCT(--(B$7:AQ$7=B8:AQ8))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...
On Jan 30, 5:36 pm, "T. Valko" wrote:



Try this:

B1:F1 = column headers
A2:A7 = row headers
B2:F7 = schedule matrix

A12 = first employee name
B12 = second employee name

=SUMPRODUCT(--(INDEX(B2:F7,MATCH(A12,A2:A7,0),)=INDEX(B2:F7,MATC H(B12,A2:A7,0),)))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...

I have a schedule that lists personnel as the row heading and dates as
the column headings that form a grid that lists the shifts the employ
works on a particular date. The shifts are labeled "A", "B", etc.

I need a formula (I'm guessing countif) that counts the number of
times two employees work together. So, if "Bob" is the label for Row
1 and "Tom" is the label for Row 2, I need to know how many times they
both have the same shift (A, B or C) in the same column during the
month which is listed across 30 columns. I've tried several
variations of countif to no avail.

Thanks,
mb

Biff,

In your example you have the employee names listed outside of the list
of row headers which I think is fouling up the works. Here is the
actual arrangement of my schedule:
B6:AQ6 -- Column Headers (dates)
A7:A14 -- Row Headers (Names)
B7:AQ14 -- Schedule Matrix

First employee name is in A7, and there are 8 employees total. I am
trying to input a formula that matches each of the bottom 7 employees'
schedule with the first one, so at the end of the row containing each
employee's schedule will be a count of how many shifts each shares
with employee #1 (A7). Based on your example, here is the formula I
wrote to compare the #2 employee (A8) with #1, which returns an #N/A
error:

=SUMPRODUCT(--(INDEX(B7:AQ14,MATCH(A7,A7:A14,0)=INDEX(B7:AQ14,MA TCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

Hmmmm....

That returns an 11 when the number of common dates between the two is
5. Not sure where that number comes from as the two don't have 11 of
anything in common that I can tell.

I can't imagine I'm the first person that's tried to do this, but I
can't find anything of the sort in countless fruitless searches.

Thanks for helping!
mb