Counting Number of Unique Values in a List based on Criteria

Can you provide us with a sample file? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Sample data file is now attached.

The Site Code is in column B and the Property Type in column C.

This is just an extract of the data that will eventually be in there.

Thanks for your interest.

Does this help any?

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Does this help any?

Please Login or Register  to view this content.

Many thanks for your solution but I maybe have not sufficiently explained what I was after.

Rather than find out how many records there are with a given Site Code and a given Property Type, I want to find out how many unique Property Types there are for a given Site Code.

For this sample of the bigger dataset, I know that there are a total of 30 unique Property Types altogether and (doing it fairly manually) can work out that within Site Codes A to E, there are 8, 2, 21, 10 and 5 unique Property Types within each respective Site Code.

It is these latter numbers that I want a formula or VBA solution to calculate such that I can list them in a Site Summary Table.

For your interest, the formula I have used for calculating the number of unique values in the list is:

=-sumproduct(-(frequency(match(datalist,datalist,0),match(datalist,datalist,0))>0))

where datalist is the range containing the data. I unashamedly found this elsewhere on the internet (at chandoo.org) and it works beautifully for this purpose but I cannot see how to extend its methodology to introduce a criteria.

Thanks again

Maybe:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by daddylonglegs

For a formula solution list A, B, C, D and E in E5 down and then in F5 use this array formula

=SUM(IF(FREQUENCY(IF(B$5:B$1500=E5,IF(C$5:C$1500<>"",MATCH(C$5:C$1500,C$5:C$1500,0))),ROW(C$5:C$1500)-ROW(C$5)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied down to F9

BTW for the total number of different property types you can use this formula

=SUMPRODUCT((C$5:C$1500<>"")/COUNTIF(C$5:C$1500,C$5:C$1500&""))

That works a treat thanks very much. I will now look at it to see if I can break down what it is doing.

Your second formula for calculating the number of unique values in a list without criteria I had seen elsewhere but the one that used "FREQUENCY" function seemed significantly faster over a large dataset (by a factor of about 8x over 20,000 records).

Thanks again.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And thank you John for this VBA solution to the same problem.

I want the answer to appear in a cell rather than a message box but I think even I can manage that modification.

Great as ever to see the combined wisdom of the forum at work so promptly and the differing solutions via formulas and VBA. I am never sure which is preferable and guess it depends on whether you come from a pure spreadsheet upbringing and so are more familiar with writing esoteric formulas or were into programming languages in which cases the sequential steps in code may be the more natural approach.

Thanks again.