Excel Count Unique Values Whole Column e.g A:A

Question

I'm looking for a way to count unique values for the whole column (A:A). Currently, I'm only able to write formulars like A1:A10 which does the job. However, when changing to A:A is returns #DIV/0

The formulars I'm currently using are:

=SUMPRODUCT(1/COUNTIF(A2:A607;A2:A607))

and

=SUM(IF(FREQUENCY(MATCH(A2:A607;A2:A607;0);MATCH(A2:A607;A2:A607;0))>0;1))

Samuel Hulla · Accepted Answer · 2018-06-25 06:57:38Z

3

This will work

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A, A:A & ""))

answered Jun 25, 2018 at 6:57

Samuel Hulla

7,2808 gold badges46 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thomasuponor Over a year ago

Did the job very well´. Thank you!

Samuel Hulla Over a year ago

Mark the answer as correct then please. Though I feel like it bos the excel speed down a little (at least for me). Might be smoother to implement simply via pivot table.

thomasuponor Over a year ago

I had to wait for 5 minutes before I was allowed to mark your answer. Yeah, agree upon the speed. However, the pivot table is not appropriate for me in this situation. Again thanks

Collectives™ on Stack Overflow

Excel Count Unique Values Whole Column e.g A:A

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related