Count

[Guest]

Hi, I am new to excel and wonder if anyone can help me with count.

I have a table e.g

aaa
aaa
bbb
bbb
ccc
ccc

I want to count the numbers of text or numbers in the column without the
repeated text or numbers. i.e the result should be 3 not 6. I need the count
to be able for number as well.

 

[Biff]

Try this:

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

Biff

 

[Guest]

Biff, I see that this formula works, but I don't understand how or why. can
you explain what the formula is doing, for example what does (A1:A6&"") do
amd the (A1:A6<>""). I've not seen that before and the "Help with this
function" doesn't explain it.

 

[Biff]

The expression: A1:A6&"", concatenates an empty string to the value in each
cell. If a cell is empty then this cell will now only contain the empty
string. This is done so that empty cells will not cause a #DIV/0! error.

The expression: A1:A6<>"", tests each cell in the range to see if it is
blank or empty so that it won't be counted.

Try this little experiment. Use the sample data posted by the OP. Enter
those values in A1:A6.

Let's breakdown the formula into its individual components.

Enter this formula in C1 and copy down to C6:

=A1<>""

Enter this formula in D1 and copy down to D6:

=COUNTIF(A$1:A$6,A1)

Enter this formula in E1 and copy down to E6:

=C1/D1

And finally, enter this formula in F1:

=SUM(E1:E6)

You get the correct result of 3. Now, clear any one of the entries in A1:A6
so that there is an empty cell and see what happens.

Now, change the formula in column D to:

=COUNTIF(A$1:A$6,A1&"")

Copy down to D6. See how that affects the result?

Biff

 

[Guest]

So as long as there are no blanks in A1:A6,
=SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6)) would yield the same result? I
think I've got it. Great explantion and example. Thank you.

 

[Biff]

So as long as there are no blanks in A1:A6,

=SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6))
would yield the same result?

Yes.

You're welcome. Thanks for the feedback!

Biff