hi Mr Aladin!

as far as i know i never heard abt the worksheet function "COUNTDIFF"
in XL!

COULD YOU PL KINDLY EXPLAIN ME ABT THAT???!!!!

-via135




Aladin Akyurek Wrote:
1] If you download & install the morefunc.xll add-in:

=COUNTDIFF(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,,"" )

which needs to be confirmed with control+shift+enter, not just with
enter.

2] With built-in functions:

=SUMPRODUCT(--((MATCH(A1:A7&"#"&B1:B7&"#"&C1:C7&"#"&D1:D7,A1:A7& "#"&B1:B7&"#"&C1:C7&"#"&D1:D7,0)=ROW(INDEX(A1:A7,0 ,0))-ROW(A1)+1)))


via135 wrote:
hi Peo!

for a single column of data the formula is ok!

can i use the same formula for data with more than one column with
duplicates?
for example

col"a" col"b" col"c" col"d"

xxx yyy 10 zzz
xyz abc 20 rst
yzx cab 10 mno
bac def 30 xyz
xyz abc 20 rst
xyz abc 10 rst
yzx cab 10 mno

-now i want to count the number of records excluding the duplicates!
in the above example 2nd & 5th, 3rd & 6th are duplicates.

if i make a count of total records without repetition, i must a get
an
answer of 5 ie.(7-2)






Peo Sjoblom Wrote:

I would personally use the variant

=SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works
as
follows

the 1/countif part returns an array of numbers, if there is one
value
unique
it will return 1,
if there are 2 values that are the same it will return 2 times 0.5
(1/2
=
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4
to
1 so
there would be 3 1

{0.333333333333333;0.5;0.5;1;1;1;0.3333333333333 33;0.5;0.5;0.333333333333333}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it
has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...

Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
. ..

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323"

wrote in

message

...

Hello All,
I have a question related to counting unique values in a column
of
data. I will try to illustrate my question. I have a column of

data

with 1000 rows. In this column there are duplicated values. I

would

like to be able to use a function count how many unique values
are

in

the column. Let's say there were 4 duplicates of 250 values in
the
column. I would like to be able to write a function to calculate

the

250. I am very familiar with Excel and am able to acheive the

number

through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through

sumproduct

and/or countif statements, but the function to calculate the
number

of

unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323


------------------------------------------------------------------------

RJL0323's Profile:

http://www.excelforum.com/member.php...o&userid=19456

View this thread:
http://www.excelforum.com/showthread...hreadid=513331








--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=513331