Hello All,
I'm a big fan of sumproduct formula but I'm a bit stuck with this one.
I have a column of persons from a database in column A, representing people in teams.
I have a list of people in one of the teams in column B
I want to count the number of time the people in column B apperas in column A.
Let me show you this :
[pre]
[/pre]
...would give me 5.
the number of people in column B is variable.
I've thought about sumproduct( --( OFFSET(A1,1,0,COUNTA(A:A)-1;1)= OFFSET(B1,1,0,COUNTA(B:B)-1,1)).
But this doesn't work.
Any idea would be greatly appreciated.
Cyril
I'm a big fan of sumproduct formula but I'm a bit stuck with this one.
I have a column of persons from a database in column A, representing people in teams.
I have a list of people in one of the teams in column B
I want to count the number of time the people in column B apperas in column A.
Let me show you this :
[pre]
Code:
Column A Column B
A A
A B
C
B
B
...would give me 5.
the number of people in column B is variable.
I've thought about sumproduct( --( OFFSET(A1,1,0,COUNTA(A:A)-1;1)= OFFSET(B1,1,0,COUNTA(B:B)-1,1)).
But this doesn't work.
Any idea would be greatly appreciated.
Cyril