Try this...
Data Range
|
A |
B |
C |
D |
E |
F |
1 |
Student |
Week |
|
Unique Students |
By Week |
|
2 |
Anna |
1 |
|
3 |
1 |
1 |
3 |
Matt |
2 |
|
|
2 |
2 |
4 |
Anna |
2 |
|
|
3 |
1 |
5 |
Steve |
3 |
|
|
4 |
2 |
6 |
Anna |
4 |
|
|
|
|
7 |
Matt |
4 |
|
|
|
|
8 |
Anna |
4 |
|
|
|
|
9 |
------ |
------ |
------ |
------ |
------ |
------ |
If you have "a lot" of data we can use a more efficient formula.
This formula entered in D2:
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
This array formula** entered in F2 and copied down:
=SUM(IF(FREQUENCY(IF(B$2:B$8=E2,MATCH(A$2:A$8,A$2:A$8,0)),ROW(A$2:A$8)-ROW(A$2)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Bookmarks