Re: counting values ignoring duplicates

Hi Bob, this also worked, many thanks, appreciated

"Bob Phillips" wrote:

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"matt3542" <matt3542@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5F4D2560-3101-4049-BF62-F76A5EE165DD@xxxxxxxxxxxxxxxx
Hi there,

I am trying to count the number of values in a column (A2:A217) ignoring
duplicated values. As an eg, applying this to the data below I would
expect
the count value to be 3. Please can anyone help, many thanks, Matt

00013270
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00018038
00023049
00023049
00023049




.

Relevant Pages

  • Re: Help with SUMIF
    ... It worked because any non-zero value is TRUE, so for the odd columns *1 ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... "Bob Phillips" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Formula to get sum from one 31 sheets to a summary sheet.
    ... "Bob Phillips" wrote: ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... This is brand new to me, please forgive me if my related communication is ...
    (microsoft.public.excel.misc)
  • Re: How can I enter monthly fee in one cell in excel?
    ... Once you are in the VBIDE yes, but in starts by taking you to the worksheet ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... "Bob Phillips" wrote: ...
    (microsoft.public.excel.setup)
  • Re: more than 30 fields in one Sum function
    ... "Bob Phillips" wrote: ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... current file format. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Dynamic average
    ... "Bob Phillips" wrote: ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... Columns D-AH: daily numbers ...
    (microsoft.public.excel.worksheet.functions)