counting names

[simoncjohn]

Hi there, i am doing a piece of work (18000+ lines) trying to ascertain a value that relates to staff numbers...

I have an extract of data that includes names, payrefs, organisation and other data surrounding payments made to staff (i.e, First Aid, Night Duty etc) - each line is unique in a sense that it relates to a type of payment made, what i am needing to calculate is the total number of staff per organisation that are in receipt of a payment (FYI member of staff may have had 20 payments - i am only wanting to show the value 1 and not 20 so that they could be counted and placed into a pivot table). I have tried various Ifcount and pivot table options to no avail. can you help please?

regards, Simon.

 

[Gerald Higgins]

Hi, welcome to the board.

Are you saying you want a count of the unique names ?

 

[Sanjeev1976]

Use :

=SUMPRODUCT(1/COUNTIF(A:A,A:A))

replace A:A with the range for the names. This would give you a count of the unique names.

Sanjeev

 

[Gerald Higgins]

Sanjeev - interesting formula, but I don't understand exactly how it works. Can you explain please ?

Also, it return as #DIV/0! error if there are blanks. Is there a neat way round that ?

 

[T. Valko]

Hi there, i am doing a piece of work (18000+ lines) trying to ascertain a value that relates to staff numbers...

I have an extract of data that includes names, payrefs, organisation and other data surrounding payments made to staff (i.e, First Aid, Night Duty etc) - each line is unique in a sense that it relates to a type of payment made, what i am needing to calculate is the total number of staff per organisation that are in receipt of a payment (FYI member of staff may have had 20 payments - i am only wanting to show the value 1 and not 20 so that they could be counted and placed into a pivot table). I have tried various Ifcount and pivot table options to no avail. can you help please?

regards, Simon.

With 18k rows of data any formula will be kind of slow to calculate. Some will be slower than others.

Try this:

Book1
	A
2	Name1
3	Name10
4	Name10
5	Name2
6	Name2
7	Name2
8	Name3
9	Name4
10	Name4
11	Name5
12	Name5
13	Name6
14	Name6
15	Name7
16	Name7
17	Name7
18	Name7
19	Name9
20	Name9
Sheet2

Array entered**:

=SUM(IF(FREQUENCY(MATCH(A2:A20,A2:A20,0),ROW(A2:A20)-ROW(A2)+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.

Assumes no empty cells within the range.