Count numbers only once

[maymano]

I have a column of numbers which represent a customer number with some repeated:

A
15
15
15
16
16
18
19
22
etc

I want to insert a formula that will count the number of customers i.e. each individual customer number will only be counted once. Thanks.

[zbor]

Here, try this (only for numbers):

=SUMPRODUCT(IF(FREQUENCY(A1:A10, A1:A10)>0, 1))

[Haseeb Avarakkan]

Try Also,

=SUM(SIGN(FREQUENCY(A:A,A:A)))

[shg]

Or, confirmed with Ctrl+Shift+Enter,

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

[zbor]

shg formula work for text too but it will give errors in case of blank cells...

Here is slightly improvement:

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

[MarvinP]

Hi maymano,

Here is a way that uses a helping column. See attached.

This should work with any combination of numbers or text.

[zbor]

Why use helper column with hundreds COUNTIF formulas after 4 ways without it?

[martindwilson]

i like
=SUM(SIGN(FREQUENCY(A:A,A:A)))

[zbor]

I agree with you...

I see Haseeb A got his second green flag Congrats and deserved

[MarvinP]

But I like helper columns!

I think that we sometimes give pretty hard formulas that the OP could not derive on his own. My thinking is that Helper Columns (many times) break the problem into smaller and simpler steps that they may get on there own in the future.

I used to be a school teacher too.

[maymano]

Thanks. Really appreciate all the input!