Counting unique records

[Kentman]

Hi guys! I've looked at the forum and searched but can't quite find what I want so:

I have a column of names (not unique as there are repeats), a column relating to the borough they were processed in. I have been able to produce a count of the number of times each person appears in the list by using =COUNTIF(Q$2:Q$5153,Q2) and copying this down but the problem I have is that each record has the number of times it appears so for one person, who appears 10 times, each record has got 10 next to it.

I need to identify each unique record, count how many times they appear in the list and total the number of repeats - any that only appear once I don't need.

Difficult to post records as these are sensitive and I can't install add-ins!

Getting stumped on this! Any help would be appreciated.

 

[Weaver]

This might help

Change your formula to

=COUNTIF(Q$2:Q2,Q2)

and copy down to the last row.

This only counts occurences in the current row and above, which might avoid this problem

each record has the number of times it appears so for one person

HTH

 

[JamesW]

Hi,

Try:

=SUM(IF(FREQUENCY(MATCH(Q2:Q5153,Q2:Q5153,0),MATCH(Q2:Q5153,Q2:Q5153,0))>0,1))

 

[Andrew Poulsom]

Another way:

=SUMPRODUCT(1/COUNTIF(Q2:Q5153,Q2:Q5153))

assuming there are no blank cells in the range.

 

[Flicker]

countif(range, value) is make sense for me because it look like select and having in SQL language.

But sumproduct(1/countif) O_O

how does it work?? Could anyone explain this for me? If it's not appropriate to ask detail here. I'm sorry. I will create a new topic.

Best Regards,
Flicker

 

[Kentman]

=SUM(IF(FREQUENCY(MATCH(Q2:Q5153,Q2:Q5153,0),MATCH(Q2:Q5153,Q2:Q5153,0))>0,1))

James: this produces the required result - thank you.

=SUMPRODUCT(1/COUNTIF(Q2:Q5153,Q2:Q5153))

Andrew: this counts all the unique records so is useful but doesn't discount those which only appear once. But thank you - always one of the first to help.

Now for the rub - I know, I'm sorry. I left this out of my original post!

I need to then split the result by which borough they are in!

Thxs

 

[Andrew Poulsom]

countif(range, value) is make sense for me because it look like select and having in SQL language.

But sumproduct(1/countif) O_O

how does it work?? Could anyone explain this for me? If it's not appropriate to ask detail here. I'm sorry. I will create a new topic.

Best Regards,
Flicker

Let's say A1:A10 contains the list:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Andrew</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Charles</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>David</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Charles</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Andrew</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Andrew</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD></TR></TBODY></TABLE>

The formula:

COUNTIF(A1:A10,A1:A10)

returns an array containing the count of each element:

{3;4;2;1;2;3;3;4;4;4}

dividing that into 1 returns:

{0.33;0.25;0.5;1;0.5;0.33;0.33;0.25;0.25;0.25}

the SUM of which is 4.

SUM could be used instead of SUMPRODUCT, but that would need to be confirmed with Ctrl+Shift+Enter.

 

[JamesW]

Glad it worked for you, for future reading try:

http://office.microsoft.com/en-us/e...nique-values-in-a-data-range-HP003056118.aspx

That's where I stol.. er 'borrowed' it from.

 

[Andrew Poulsom]

Which column is the borough in?

 

[Kentman]

A named range called 'BOROUGH'