Excel formula for calculating unique occurrences in a column grouped by another column

Question

I need to build a single Excel formula for calculating the third column in the table below based on the previous two columns.

1. First column shows the name of the active user
2. Second column shows which week number the activity occurred
3. The third column should in every row show the total number of unique weeks that the user in the first colum has been active ever.

---

------------------------------------------------
NAME   ACTIVE-IN-WEEK-NO NUM-UNIQUE-ACTIVE-WEEKS (Need a formula for this one)
------------------------------------------------
JOHN  |  50             |  2
ADAM  |  48             |  3
PETER |  48             |  1
JOHN  |  50             |  2
JOHN  |  50             |  2
ADAM  |  45             |  3
ADAM  |  40             |  3
PETER |  48             |  1
JOHN  |  45             |  2
-------------------------------------------------

Answer 1

For this requirement you need to use Microsoft Query , refer the link given below :

http://www.excel-easy.com/examples/microsoft-query.html

To Fix this you need to write sql statement in microsoft query.

Hope this Helps.

Answer 2

Assuming your table is in A1:B10 (with headers in row 1), this array formula in C2:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=A2,$B$2:$B$10),$B$2:$B$10)>0,1))

Copy down as required.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).