Count Uniques

Hi,

I have two columns of data - one with IDs and another with States. In column F, I have the deduplicated list of unique IDs, but I need helping counting the number of unique states that are associated with each ID from columns B:C. Any help would be much appreciated!

Thanks,
John

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

To do it efficiently sort the data by ID & State, then in adjacent cell (D)

Please Login or Register  to view this content.

Your results are then a basic SUMIF which will perform quickly

Please Login or Register  to view this content.

Another alternative - again working from sorted data

Please Login or Register  to view this content.

which might be quicker...

I would agree with DO.

One more way, assuming you are using XL 2007 as implied my attachment... and if you don't want to sort...

in D3:

=COUNTIFS(B$3:B3,B3,C$3:C3,C3)

copied down

Then in G3:

=COUNTIFS(B:B,F3,D:D,1)

copied down.

Originally Posted by John Bates

...the following:

D3:
=IF(B3&C3<>"",IF(B3=B2,D2+C3<>C2,1),0)
copied down
(assumes D2 to be blank)

G3:
=LOOKUP(F3,B:D)*(LOOKUP(F3,B:B)=F3)
copied down

It seems to throw errors and not work. Any thoughts?

It requires the data (B:C) be sorted by ID & State and in present form it also requires D2 to be blank (else 0).

If you have values other than IDs located throughout Column B (ie these ID references only represent a portion of the column content) then limit the LOOKUP ranges accordingly (eg B2:D170000 rather than B:D etc...)

whoops - typo on my part - I should really read things !

Please Login or Register  to view this content.

note the parentheses around C3<>C2

Regards the summary LOOKUP calc used in conjunction with above...

LOOKUP is very fast because it utilises binary search algorithm and as such processes only a tiny subset of the precedent range(s)
It follows that where the precedent range is vast the savings can be significant as a result.
Binary Searches of this nature require the data be sorted accordingly.

We use 2 LOOKUPs as it is an approximate match so the 2nd is used to ensure the value returned is for the correct ID - if not result will be 0.
Given the nature of LOOKUP function calculating 2 LOOKUPs per cell will still be blindingly fast.

LOOKUP in this context is very similar to using VLOOKUP with range_lookup set to TRUE.

Overall LOOKUP is an invaluable function as it offers the user a lot of flexibility.