How do I create a function to count my data

[HL]

I have a worksheet with three important columns. Column A contains last
names, Column B contains the number of times that a particular name is
duplicated within my spreadsheet (i.e =COUNTIF(A:A,A2) ) this statement
returns a number like 1, 2, 3, etc. Finally, Column C contains word Active or
Inactive.

What I want to do is
1) Assign a numeric value to the user (last name) based upon that fact that
he/she is listed once or several times. (i.e. Listed once value =1, listed
twice value = 2, etc.)

2) Then get a subtotal of the number of the active users and get a subtotal
of the number of the inactive users by using the calculations described above.

Can anyone give me suggestions? I really need help with this.

 

[Frank Kabel]

Hi
for the number of active users use the following formulas (using a
different approach though - without helper columns):
Enter the formula as array formula (with CTRL+SHIFT+ENTER):
=COUNT(1/FREQUENCY(IF((C1:C20="Active")*(A1:A20<>""),MATCH(A1:A20,A1:A2
0,0)),ROW(INDIRECT("1:"&COUNTA(A1:A20)))))

and for the inactive users try:
=COUNT(1/FREQUENCY(IF((C1:C20="Inactive")*(A1:A20<>""),MATCH(A1:A20,A1:
A20,0)),ROW(INDIRECT("1:"&COUNTA(A1:A20)))))

 

[HL]

Frank,
I typed the exact formula given below however I only get a 0 in the cell. Am
I doing something wrong?

 

[Frank Kabel]

Hi
you have to array enter it. Commit the formula with CTRL+SHIFT+ENTER