CODEWITHSUNDEEP
excelcountdistinct
Joe Laert
Joe Laert

Reputation: 136

How to count distinct entries in a column in Excel

I know that the formula: {=SUM(1/COUNTIF(A1:A8,A1:A8))} will work for counting the distinct entries in the column A1 to A8.

But my question is, what exactly is this formula doing? I can't seem to follow the logic of this array formula.

Upvotes: 0

Views: 130

Answers (2)

rwilson
rwilson

Reputation: 2135

Using F9 really comes in handy here. Suppose the below values are in A1:A8:

dog
cat
dog
cat
cat
dog
cat
cat

In the formula bar highlight COUNTIF(A1:A8,A1:A8) and press F9 and you will see:

{3;5;3;5;5;3;5;5}

Because there are 3 dogs and 5 cats in the list, these are the numbers that are returned by the countif formula for each appropriate type.

Now undo with CTRL+ Z or press ESC to start over. This time highlight 1/COUNTIF(A1:A8,A1:A8) and you will see:

{0.333333333333333;0.2;0.333333333333333;0.2;0.2;0.333333333333333;0.2;0.2}

Because there are 3 dogs in the list, 1/3 produces .3333333. Now .3333333 appears in the list in the same position that dog appears in the list. Add up all the .3333333 and you get 1.

Do the same for cats. 5 Cats. 1/5 produces .2, and so on.

When in doubt how a formula works, highlight portions of the formula and press F9 and you can see what it's calculating.

Upvotes: 2

nwhaught
nwhaught

Reputation: 1592

Assume a value x in A1, blanks in A2:A8. If you use the Evaluate Formula tool, you'll see that the first step of the array formula provides an array of COUNTIFs:

=SUM(1/{1,7,7,7,7,7,7,7})

Note that there is 1 1, and 7 7s, because there is one value x, and seven blank values.

Remember that (1/n)*n = 1. So in this example,

(1/7) * 7 = 1

(1/1) * 1 = 1

Sum these results, and it's as easy as 1 + 1 = 2. :-)

Upvotes: 2

Related Questions