Re: counting values ignoring duplicates

=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: Workday fxn not recognizing Sat/Sun/Holiday start
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... and Holidays!R2C1:R18C1 would be my holiday ... The issue I'm having is that it seems to be ignoring my If statement for ...
    (microsoft.public.excel.programming)
  • Re: counting values ignoring duplicates
    ... "Bob Phillips" wrote: ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... Please can anyone help, many thanks, Matt ...
    (microsoft.public.excel.worksheet.functions)
  • Re: drop down lists
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... small and users will struggle to use these ... Matt ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Selection.Cells vs Range reference - strange behaviour (using
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... parentheses to indicate the parameters to be passed. ... I have a series of subs for formatting text along the lines of ...
    (microsoft.public.excel.programming)
  • 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)