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.333333333333333;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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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" <[email protected]> wrote in message
>>>news:%[email protected]...
>>>
>>>>=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))
>>>>
>>>>--
>>>>
>>>>HTH
>>>>
>>>>Bob Phillips
>>>>
>>>>(remove nothere from the email address if mailing direct)
>>>>
>>>>"RJL0323" <[email protected]>
>>
>>wrote in
>>
>>>>message
>>
>>news:[email protected]...
>>
>>>>>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
>>>>>
>>>>
>>>>
>>>
>
>