In a column I have hundreds of airway bills. I need to calculate how many
different numbers I have in that report. Not the total (I can go to the last
row for that) and not the frequency. For example:

9140266155
9182187486
9182187556
9182187556
9182187464
9182187464
9140266166
9140266166
9140266170
9140266170
9140266181

Here the formula here should give me 7 which is the number of different
AWB's there are.

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


This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

(If you enter it the wrong way you will get 1.)
--
Gary''s Student - gsnu200851


"Byron720" wrote:

In a column I have hundreds of airway bills. I need to calculate how many
different numbers I have in that report. Not the total (I can go to the last
row for that) and not the frequency. For example:

9140266155
9182187486
9182187556
9182187556
9182187464
9182187464
9140266166
9140266166
9140266170
9140266170
9140266181

Here the formula here should give me 7 which is the number of different
AWB's there are.

From the XL help file "Count the number of unique values by using functions":

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))
Note that this needs to be entered as an array (use Ctrl+Shift+Enter)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Byron720" wrote:

In a column I have hundreds of airway bills. I need to calculate how many
different numbers I have in that report. Not the total (I can go to the last
row for that) and not the frequency. For example:

9140266155
9182187486
9182187556
9182187556
9182187464
9182187464
9140266166
9140266166
9140266170
9140266170
9140266181

Here the formula here should give me 7 which is the number of different
AWB's there are.

In a column I have hundreds of airway bills.

The following formulas are SLOW to calculate if you had 1000's of rows of
data.

If there are no empty cells within the range:

=SUMPRODUCT(1/COUNTIF(A1:A11,A1:A11))

If there might be empty cells within the range:

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

--
Biff
Microsoft Excel MVP


"Byron720" wrote in message
...
In a column I have hundreds of airway bills. I need to calculate how many
different numbers I have in that report. Not the total (I can go to the
last
row for that) and not the frequency. For example:

9140266155
9182187486
9182187556
9182187556
9182187464
9182187464
9140266166
9140266166
9140266170
9140266170
9140266181

Here the formula here should give me 7 which is the number of different
AWB's there are.