Simple Countif Unique

[SStultzCostello]

Hi All. I'm new to Chandoo.org. I'm hoping you can help me. I searched for a formula on the site but found things way over my head and used for more involved requests. I think what I am looking for is quite simple. I have a list of addresses that I exported from a database. In the full sheet, I have a column for names. Each name is listed on its own row. Spouses share the same address. So, in the sample data (attached) you will see I have several addresses duplicated. I want to know how many unique addresses I have in the list. It seems like there should be a simple formula like a Countif formula - but where I can tell it to only count the unique data. Thank you for your help.

 

[herofox]

Hello ,you can use this

=SUMPRODUCT(($D$6:$D$1000<>"")/COUNTIF($D$6:$D$1000,$D$6:$D$1000&""))

 

[Peter Bartholomew]

Assuming that you name your list 'Address' the traditional formula would be
= SUM( 1 / COUNTIFS( Address, Address ) )
[this may require CSE to commit an array formula]
The newest releases of Excel will accept
= ROWS( UNIQUE( Address ) )

 

[GraH - Guido]

Hi SStultzCostello,

If you are looking for a simple solution, it can be advised to work with a helper column that highlights the first time a value is found.
Then a sum on that column does also the trick.

[E6]= (COUNTIFS($D$6:D6,D6)=1)*1
For total unique count: SUM(E:E)