Help with an array and SUMPRODUCT
 

I am not positive that SUMPRODUCT is the right function to accomplish what I
am trying to do so Ill start by describing my objective. I suspect the
solution I am looking for lies with some variation of SUMPRODUCT entered as
an array.

On one spreadsheet, in column A, I have a list of the names of people,
starting in row 2.
Across row 1, I have a list of meetings by date and topic, starting in
column B.
At the intersection of each row and column in the resulting array, I have a
binary value: 1 if that person attended that meeting, 0 if that person did
not attend that meeting.

In another spreadsheet, in column A, I have a list of the same names of
people, starting in row 2.
Across row 1, I have the same names transposed, starting in column B.
At the intersection of each row and column in the resulting array, I want to
calculate the total number of times that those two people have attended the
same meeting.

In other words, I want to look at the name for that given row and the name
for that given column then count the number of meetings (columns) in the
other spreadsheet where both have a value of 1. As an added layer of
complexity, I am hoping to find a flexible enough function such that as I
continue to add meetings (columns) I dont have to update the function and it
will still calculate the new value.

I have some knowledge of VBA so even workaround using code would work for me.

For whatever it is worth, I am running Office 2007 with the Analysis Toolpak
add-in installed.

The help of the community is always greatly appreciated. Please let me know
if I can clarify or provide more information.

THANKS!

Help with an array and SUMPRODUCT
 

I have a binary value: 1 if that person
attended that meeting, 0 if that person
did not

Try this...

Main data table on Sheet1 in the range A1:F11

Matrix table on Sheet2. Row headers in the range A2:A11. Column headers in
the range B1:K1.

Enter this formula on Sheet2 in cell B2:

=IF($A2=B$1,0,SUMPRODUCT(--(INDEX(Sheet1!$B$2:$F$11,MATCH($A2,Sheet1!$A$2:$A$ 11,0),0)=1),--(INDEX(Sheet1!$B$2:$F$11,MATCH(B$1,Sheet1!$A$2:$A$ 11,0),0)=1)))

Copy across to K2 then down to B11:K11.

--
Biff
Microsoft Excel MVP


"xlcharlie" wrote in message
...
I am not positive that SUMPRODUCT is the right function to accomplish what
I
am trying to do so I'll start by describing my objective. I suspect the
solution I am looking for lies with some variation of SUMPRODUCT entered
as
an array.

On one spreadsheet, in column A, I have a list of the names of people,
starting in row 2.
Across row 1, I have a list of meetings by date and topic, starting in
column B.
At the intersection of each row and column in the resulting array, I have
a
binary value: 1 if that person attended that meeting, 0 if that person did
not attend that meeting.

In another spreadsheet, in column A, I have a list of the same names of
people, starting in row 2.
Across row 1, I have the same names transposed, starting in column B.
At the intersection of each row and column in the resulting array, I want
to
calculate the total number of times that those two people have attended
the
same meeting.

In other words, I want to look at the name for that given row and the name
for that given column then count the number of meetings (columns) in the
other spreadsheet where both have a value of 1. As an added layer of
complexity, I am hoping to find a flexible enough function such that as I
continue to add meetings (columns) I don't have to update the function and
it
will still calculate the new value.

I have some knowledge of VBA so even workaround using code would work for
me.

For whatever it is worth, I am running Office 2007 with the Analysis
Toolpak
add-in installed.

The help of the community is always greatly appreciated. Please let me
know
if I can clarify or provide more information.

THANKS!

Help with an array and SUMPRODUCT
 

It worked! Thanks so much.

I never cease to be amazed by how fast the crowd can solve what is for me an
intractable problem.

"T. Valko" wrote:

I have a binary value: 1 if that person
attended that meeting, 0 if that person
did not

Try this...

Main data table on Sheet1 in the range A1:F11

Matrix table on Sheet2. Row headers in the range A2:A11. Column headers in
the range B1:K1.

Enter this formula on Sheet2 in cell B2:

=IF($A2=B$1,0,SUMPRODUCT(--(INDEX(Sheet1!$B$2:$F$11,MATCH($A2,Sheet1!$A$2:$A$ 11,0),0)=1),--(INDEX(Sheet1!$B$2:$F$11,MATCH(B$1,Sheet1!$A$2:$A$ 11,0),0)=1)))

Copy across to K2 then down to B11:K11.

--
Biff
Microsoft Excel MVP


"xlcharlie" wrote in message
...
I am not positive that SUMPRODUCT is the right function to accomplish what
I
am trying to do so I'll start by describing my objective. I suspect the
solution I am looking for lies with some variation of SUMPRODUCT entered
as
an array.

On one spreadsheet, in column A, I have a list of the names of people,
starting in row 2.
Across row 1, I have a list of meetings by date and topic, starting in
column B.
At the intersection of each row and column in the resulting array, I have
a
binary value: 1 if that person attended that meeting, 0 if that person did
not attend that meeting.

In another spreadsheet, in column A, I have a list of the same names of
people, starting in row 2.
Across row 1, I have the same names transposed, starting in column B.
At the intersection of each row and column in the resulting array, I want
to
calculate the total number of times that those two people have attended
the
same meeting.

In other words, I want to look at the name for that given row and the name
for that given column then count the number of meetings (columns) in the
other spreadsheet where both have a value of 1. As an added layer of
complexity, I am hoping to find a flexible enough function such that as I
continue to add meetings (columns) I don't have to update the function and
it
will still calculate the new value.

I have some knowledge of VBA so even workaround using code would work for
me.

For whatever it is worth, I am running Office 2007 with the Analysis
Toolpak
add-in installed.

The help of the community is always greatly appreciated. Please let me
know
if I can clarify or provide more information.

THANKS!


.

Help with an array and SUMPRODUCT
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"xlcharlie" wrote in message
...
It worked! Thanks so much.

I never cease to be amazed by how fast the crowd can solve what is for me
an
intractable problem.

"T. Valko" wrote:

I have a binary value: 1 if that person
attended that meeting, 0 if that person
did not

Try this...

Main data table on Sheet1 in the range A1:F11

Matrix table on Sheet2. Row headers in the range A2:A11. Column headers
in
the range B1:K1.

Enter this formula on Sheet2 in cell B2:

=IF($A2=B$1,0,SUMPRODUCT(--(INDEX(Sheet1!$B$2:$F$11,MATCH($A2,Sheet1!$A$2:$A$ 11,0),0)=1),--(INDEX(Sheet1!$B$2:$F$11,MATCH(B$1,Sheet1!$A$2:$A$ 11,0),0)=1)))

Copy across to K2 then down to B11:K11.

--
Biff
Microsoft Excel MVP


"xlcharlie" wrote in message
...
I am not positive that SUMPRODUCT is the right function to accomplish
what
I
am trying to do so I'll start by describing my objective. I suspect
the
solution I am looking for lies with some variation of SUMPRODUCT
entered
as
an array.

On one spreadsheet, in column A, I have a list of the names of people,
starting in row 2.
Across row 1, I have a list of meetings by date and topic, starting in
column B.
At the intersection of each row and column in the resulting array, I
have
a
binary value: 1 if that person attended that meeting, 0 if that person
did
not attend that meeting.

In another spreadsheet, in column A, I have a list of the same names of
people, starting in row 2.
Across row 1, I have the same names transposed, starting in column B.
At the intersection of each row and column in the resulting array, I
want
to
calculate the total number of times that those two people have attended
the
same meeting.

In other words, I want to look at the name for that given row and the
name
for that given column then count the number of meetings (columns) in
the
other spreadsheet where both have a value of 1. As an added layer of
complexity, I am hoping to find a flexible enough function such that as
I
continue to add meetings (columns) I don't have to update the function
and
it
will still calculate the new value.

I have some knowledge of VBA so even workaround using code would work
for
me.

For whatever it is worth, I am running Office 2007 with the Analysis
Toolpak
add-in installed.

The help of the community is always greatly appreciated. Please let me
know
if I can clarify or provide more information.

THANKS!


.