Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

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

Countif cells two rows in corresponding columns
 

On Feb 2, 2:01*pm, "T. Valko" wrote:
Here's my understanding of what you want to do:

...............Date1.....Date2.....Date3.....formu la
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

That is exactly what I want to do.

Countif cells two rows in corresponding columns
 

The formula I suggested does *exactly* that.

...........A...........B............C............D ...........E
1...................Date1.....Date2.....Date3..... formula
2...Name1......A............B............C........ ............
3...Name2......C............B............A........ ...........
4...Name3......A............B............C........ ...........

Based on the above table, this formula entered in E3 and copied down returns
the desired results:

=SUMPRODUCT(--(B$2:D$2=B3:D3))

If you want to exclude empty cells from being counted as matches:

...........A...........B............C............D ...........E
1...................Date1.....Date2.....Date3..... formula
2...Name1.....................B............C...... ..............
3...Name2.....................B............A...... ..............
4...Name3......A............B............C........ ............

=SUMPRODUCT(--(B$2:D$2<""),--(B3:D3<""),--(B$2:D$2=B3:D3))


--
Biff
Microsoft Excel MVP


"michaelberrier" wrote in message
...
On Feb 2, 2:01 pm, "T. Valko" wrote:
Here's my understanding of what you want to do:

...............Date1.....Date2.....Date3.....formu la
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

That is exactly what I want to do.

Countif cells two rows in corresponding columns
 

On Feb 2, 7:07*pm, "T. Valko" wrote:
The formula I suggested does *exactly* that.

..........A...........B............C............D. ..........E
1...................Date1.....Date2.....Date3..... formula
2...Name1......A............B............C........ ............
3...Name2......C............B............A........ ...........
4...Name3......A............B............C........ ...........

Based on the above table, this formula entered in E3 and copied down returns
the desired results:

=SUMPRODUCT(--(B$2:D$2=B3:D3))

If you want to exclude empty cells from being counted as matches:

..........A...........B............C............D. ..........E
1...................Date1.....Date2.....Date3..... formula
2...Name1.....................B............C...... ..............
3...Name2.....................B............A...... ..............
4...Name3......A............B............C........ ............

=SUMPRODUCT(--(B$2:D$2<""),--(B3:D3<""),--(B$2:D$2=B3:D3))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...
On Feb 2, 2:01 pm, "T. Valko" wrote:



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

...............Date1.....Date2.....Date3.....formu la
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

That is exactly what I want to do.

The last one was perfect. Thanks for helping me get this right.

Countif cells two rows in corresponding columns
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"michaelberrier" wrote in message
...
On Feb 2, 7:07 pm, "T. Valko" wrote:
The formula I suggested does *exactly* that.

..........A...........B............C............D. ..........E
1...................Date1.....Date2.....Date3..... formula
2...Name1......A............B............C........ ............
3...Name2......C............B............A........ ...........
4...Name3......A............B............C........ ...........

Based on the above table, this formula entered in E3 and copied down
returns
the desired results:

=SUMPRODUCT(--(B$2:D$2=B3:D3))

If you want to exclude empty cells from being counted as matches:

..........A...........B............C............D. ..........E
1...................Date1.....Date2.....Date3..... formula
2...Name1.....................B............C...... ..............
3...Name2.....................B............A...... ..............
4...Name3......A............B............C........ ............

=SUMPRODUCT(--(B$2:D$2<""),--(B3:D3<""),--(B$2:D$2=B3:D3))

--
Biff
Microsoft Excel MVP

"michaelberrier" wrote in message

...
On Feb 2, 2:01 pm, "T. Valko" wrote:



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

...............Date1.....Date2.....Date3.....formu la
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

That is exactly what I want to do.

The last one was perfect. Thanks for helping me get this right.