X
xlcharlie
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!
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!