Countif cells two rows in corresponding columns

[michaelberrier]

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

 

[T. Valko]

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,MATCH(B12,A2:A7,0),)))

 

[michaelberrier]

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,MATCH(B12,A2:A7,0),)))

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,MATCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

 

[T. Valko]

Try this...

Entered in AR8 and copied down to AR14:

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

--
Biff
Microsoft Excel MVP

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,MATCH(B12,A2:A7,0),)))

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,MATCH
(A8,A7:A14,0),))))

Thanks for yours and any help,
mb

 

[michaelberrier]

Try this...

Entered in AR8 and copied down to AR14:

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

--
Biff
Microsoft Excel MVP







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,MATCH
(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

 

[T. Valko]

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

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

Try this...

Entered in AR8 and copied down to AR14:

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

--
Biff
Microsoft Excel MVP







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,MATCH
(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

 

[michaelberrier]

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

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








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.

 

[T. Valko]

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$2=B33))

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$2<>""),--(B33<>""),--(B$2$2=B33))


--
Biff
Microsoft Excel MVP

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

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








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.

 

[michaelberrier]

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$2=B33))

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$2<>""),--(B33<>""),--(B$2$2=B33))

--
Biff
Microsoft Excel MVP









That is exactly what I want to do.

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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$2=B33))

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$2<>""),--(B33<>""),--(B$2$2=B33))

--
Biff
Microsoft Excel MVP









That is exactly what I want to do.

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