COUNT Question

[jcl408]

Is there a way to do a COUNT of unique items in a column? So if you had a data set with a bunch of entries saying say WIDGETS, GADGETS and GIZMOS, of say 1000 rows alternating between widgets, gadgets and gizmos, is there a formula that would show the count as only 3?

 

[T. Valko]

Is there a way to do a COUNT of unique items in a column? So if you had a data set with a bunch of entries saying say WIDGETS, GADGETS and GIZMOS, of say 1000 rows alternating between widgets, gadgets and gizmos, is there a formula that would show the count as only 3?

Assuming there are no empty cells within the range.

Array entered**:

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

 

[Scott Huish]

=SUM(1/COUNTIF(A1:A1000,A1:A1000))

Confirm with CTRL-SHIFT-ENTER

 

[jcl408]

Thanks guys

 

[T. Valko]

Thanks guys

You're welcome!

 

[jcl408]

Is there a way to do this within a defined column&row set? Like E2:E2356?

 

[T. Valko]

Is there a way to do this within a defined column&row set? Like E2:E2356?

Not sure what you mean, but...

Array entered:

=SUM(IF(FREQUENCY(MATCH(E2:E2356,E2:E2356,0),ROW(E2:E2356)-ROW(E2)+1),1))

 

[jcl408]

Was forgetting to SHIFT+CTRL+ENTER. Sorry haha it's getting late over here

 

[T. Valko]

Was forgetting to SHIFT+CTRL+ENTER.

Yeah, that gets a lot of people.

You can try this normally entered version but it's a bit slow to calculate on a range that size.

Just a normal enter:

=SUMPRODUCT(1/COUNTIF(E2:E2356,E2:E2356))